UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Need To Loop Through Table Instead Of Array, Access 2010    
 
   
SubPlanner
post Dec 13 2016, 12:55 PM
Post#1



Posts: 34
Joined: 22-April 16



I am a nubie at Access but still need help from time to time. I have inherited a data base from some one who does not work here anymore and I have a looping problem.
I need to loop through a field in a table for Building/Floor information instead of using an array.
Here is an example of the VBA I have. (This is were I would like to loop through a table instead of this array) (Works good but we have to keep changing it)
CODE
Function testUpdate()
    Dim uSeats As New updateSeats
    Dim filArr() As Variant
    
      
    filArr = Array("BuildingA21-01_Spaces", "BuildingA21-02_Spaces", "BuildingA21-03_Spaces", "BuildingB31-01_Spaces", _
    "BuildingB31-02_Spaces", "BuildingC4-01_Spaces", "BuildingC4-0B_Sapces", "BuildingC4-02_Spaces", "BuildingC4-03_Spaces")

     updateSeats (filArr)
    
End Function

The next step this does is run some updates from another string of code called "updateSeats" as you can see it uses the (filArr). This part is okay.

What I have been asked to do is create an alternate method for doing this.
This websites has already helped me on that topic. (Needed to loop through folders and subfolders to create links in the DB. and list the names in a table)
But all that aside, I think if I can get over this hurdle of looping through a list from a table instead of this array I will be golden.
The reason we need to do this is the names of the documents that this db needs to connect to will be changing from time to time as we add new buildings and remove others.
That way the db will automatically read those new names and populate them into this table. (Example below).

Table Name is: Files
Column Name is: FName

Thanks for any help you can offer.
SubPlanner
Go to the top of the page
 
Doug Steele
post Dec 13 2016, 02:21 PM
Post#2


UtterAccess VIP
Posts: 21,490
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Are you saying that there Files would have 9 rows in it, with FName has values "BuildingA21-01_Spaces", "BuildingA21-02_Spaces", "BuildingA21-03_Spaces", "BuildingB31-01_Spaces", "BuildingB31-02_Spaces", "BuildingC4-01_Spaces", "BuildingC4-0B_Sapces", "BuildingC4-02_Spaces" and "BuildingC4-03_Spaces"?

You'd open a Recordset to return the data:

CODE
Dim rs As DAO.Recordset
Dim strSQL As String
  
  strSQL = "SELECT FName FROM Files ORDER BY Fname;"
  Set rs = CurrentDb.OpenRecordset(strSQL)
  Do While rs.EOF = False
    ' Refer to rs!FName to get the current value
    rs.MoveNext
  Loop
  
  rs.Close
  Set rs = Nothing

--------------------
Go to the top of the page
 
BruceM
post Dec 13 2016, 02:30 PM
Post#3


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


Doug, I thiink the idea is that the recordset is converted to an array, which is passed to another function. If so, the thing that needs to happen within the loop is to add the field value to an array.

However, now that I think about it, perhaps it would be better to pass the recordset itself to the function.

I had an idea that GetRows could be used for the purpose of creating an array from a recordset, but either the documentation is poor or it is an unnecesssarily complicated way of getting the job done. I had tried GetRows in the past, and had to abandon the attempt. I thought maybe I could figure it out now, but no luck.
Go to the top of the page
 
Doug Steele
post Dec 13 2016, 02:59 PM
Post#4


UtterAccess VIP
Posts: 21,490
Joined: 8-January 07
From: St. Catharines, ON (Canada)


You could be right, Bruce. Guess we'll have to wait for SubPlanner to come back and explain.

--------------------
Go to the top of the page
 
moke123
post Dec 13 2016, 08:35 PM
Post#5



Posts: 1,148
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



QUOTE
Table Name is: Files
Column Name is: FName


judging from the above, he is using Allen Browne's List Files to Table procedure.
Go to the top of the page
 
SubPlanner
post Dec 14 2016, 06:05 AM
Post#6



Posts: 34
Joined: 22-April 16



Thanks for the responses, I hope I can explain this in a better manner.
Basically, I need to point to the rows within the table named "Files" to the column named "FName" for the call code "UpdateSeats"
These names are named the same as the links to the Excel workbooks that the array is looking for.

The "UpdateSeats" routine basically has a handful of update and append SQL Query Def's that use various fields from the Excel Links.
These are the links I spoke at the start of the string.

So what I am trying to do is: Make the database more automatic by,
1. Delete all existing Excel links. (Have this working now).
2. Clear the table "Files" of all data. (Have this working now).
3. Loop through a set of folders and subfolders to refresh the table and create new links. (Have this working now).
4. Create a routine so the "UpdateSeats" module can loop through the table names instead of the array (Need This !!)
Otherwise, every time we add or delete Excel documents from our folders, I would have to rebuild the array manually.


Thanks for you time folks.
SubPlanner.
Go to the top of the page
 
gemmathehusky
post Dec 14 2016, 06:55 AM
Post#7


UtterAccess VIP
Posts: 4,432
Joined: 5-June 07
From: UK


does it work "as is"?

The reason I ask is that modifying an established solution can be fiendishly difficult.

Maintaining and changing other peoples work is never easy, especially if you do not have a full understanding of the application/processes.

eg, You need to trace the way that the array works, and what the array processing function does, before you start making changes.
Is the array disposed of afterwards, or does it retain a life, and get used elsewhere. Is it possible that other people using the system might affect the process if you use a table/recordset rather than the array.


good luck , anyway.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
BruceM
post Dec 14 2016, 07:27 AM
Post#8


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


It is difficult to be specific about the following without seeing the UpdateSeats function, but if BuildingB31-01_Spaces etc. are tables the architecture is awkward. Can you clarify the nature of the data in the table?
Go to the top of the page
 
SubPlanner
post Dec 14 2016, 09:35 AM
Post#9



Posts: 34
Joined: 22-April 16



The array at this time is hard coded. I have to change it whenever we add or remove buildings (Excel Workbooks) from the folder/file locations.
I am the only one working in this program.
But of course all the managers use the db as a tool for occupancy and capacity reporting.

SubPlanner
Go to the top of the page
 
SubPlanner
post Dec 14 2016, 09:43 AM
Post#10



Posts: 34
Joined: 22-April 16



Here is the UpdateSeats routine. Hope this helps shed a little more light

CODE
Function updateSeats(ByRef fileNames As Variant) 'from Array in module testUpdate
    Dim fil As Variant
    Dim fl As Variant
    Dim SQL As String
    Dim filRs As DAO.Recordset
    Dim delQdef As QueryDef
    Dim updateQdef As QueryDef
    Dim insertQdef As QueryDef
    Dim flIdQdef As QueryDef 'our prepared statments
    Dim uFl As New updateFloors 'if the floor doesn't exist we need to insert it before continuing
    Dim flRS As Recordset 'we need this for the result of the prepared statment
    
    Dim errBit As Boolean
    
    errBit = False
    
    'Everything that touches the DB should be a prepared statment
    Set delQdef = CurrentDb.QueryDefs("Delete_From_Seats")
    Set updateQdef = CurrentDb.QueryDefs("UpdateSeats")
    Set insertQdef = CurrentDb.QueryDefs("InsertSeats")
    Set flIdQdef = CurrentDb.QueryDefs("getFloorId")
    
    'Every file that we pull from
    
    For Each fil In fileNames
    
        SQL = "SELECT * FROM [" & fil & "];"
    
        Set filRs = CurrentDb.OpenRecordset(SQL)
        
        If Not filRs.EOF Then
            filRs.MoveFirst
        End If
        
        Do Until filRs.EOF
            
            'Check if the floor exists and if not call the floor to insert it
            flIdQdef.Parameters("fl") = filRs("Building and Floor #")
            
            Set flRS = flIdQdef.OpenRecordset
            
            If flRS.EOF Then
'                uFl.insertFloor (filRs("Building and Floor #")) 'This should recursivly add nonexistant buildings and sites
            End If
            
            flIdQdef.Parameters("fl") = filRs("Building and Floor #") 'This needs to be re set to make sure we have the new floor we inserted
            
            Set flRS = flIdQdef.OpenRecordset
            
            'Delete removed seats
      
            'All business logic should be on a different tier

'*** Excel Linked Data Fields ****      
            'Here we call the stored procedure to insert the data
            updateQdef.Parameters("s") = filRs("Seat Number")
            updateQdef.Parameters("a") = filRs("Calculated Area")
            updateQdef.Parameters("occ") = filRs("Occupancy")
            updateQdef.Parameters("cap") = filRs("Capacity")
            updateQdef.Parameters("fID") = flRS("ID")  
            '''updateQdef.Parameters("fID") = flRS("Floor")
            updateQdef.Parameters("ss") = filRs("Space Status")
            updateQdef.Parameters("sf") = filRs("Space Function")
            updateQdef.Parameters("st") = filRs("Space Type")
            updateQdef.Parameters("hd") = filRs("Home Department")
            updateQdef.Parameters("sn") = filRs("Space Name")
            updateQdef.Parameters("pres") = filRs("President")
            updateQdef.Parameters("vp") = filRs("Vice President")
            updateQdef.Parameters("dir") = filRs("Director")
            updateQdef.Parameters("ms") = filRs("Mail Stop")
            If Not InStr(fil, "Building7-01_Spaces") Then
           updateQdef.Parameters("mn") = filRs("Master Name")
            End If
            updateQdef.Parameters("shpnm") = filRs("Shape Name")
            updateQdef.Parameters("bf") = filRs("Building and Floor #")
            updateQdef.Parameters("sc") = ""
            
            insertQdef.Parameters("s") = filRs("Seat Number")
            insertQdef.Parameters("a") = filRs("Calculated Area")
            insertQdef.Parameters("occ") = filRs("Occupancy")
            insertQdef.Parameters("cap") = filRs("Capacity")
            insertQdef.Parameters("fID") = flRS("ID")
            '''insertQdef.Parameters("fID") = flRS("Floor")
            insertQdef.Parameters("ss") = filRs("Space Status")
            insertQdef.Parameters("sf") = filRs("Space Function")
            insertQdef.Parameters("st") = filRs("Space Type")
            insertQdef.Parameters("hd") = filRs("Home Department")
            insertQdef.Parameters("sn") = filRs("Space Name")
            insertQdef.Parameters("pres") = filRs("President")
            insertQdef.Parameters("vp") = filRs("Vice President")
            insertQdef.Parameters("dir") = filRs("Director")
            insertQdef.Parameters("ms") = filRs("Mail Stop")
            If Not InStr(fil, "Building7-01_Spaces") Then
            insertQdef.Parameters("mn") = filRs("Master Name")
            End If
            insertQdef.Parameters("shnm") = filRs("Shape Name")
            insertQdef.Parameters("bf") = filRs("Building and Floor #")
            insertQdef.Parameters("sc") = ""
            
            
            updateQdef.Execute
            
            'if no records get updated insert the new one
            If updateQdef.RecordsAffected = 0 Then
                insertQdef.Execute
            End If
            
            
            filRs.MoveNext
        Loop
    
    Next fil


As you can see, there are a few qrydef's that use the linked excel workbooks that are listed in the array

SUbPlanner
Go to the top of the page
 
Doug Steele
post Dec 14 2016, 09:52 AM
Post#11


UtterAccess VIP
Posts: 21,490
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Since you want to get the file names from the table, not an array, there's no need to pass anything to the function.

CODE
Function updateSeats()
    Dim fil As String
    Dim fl As Variant
    Dim SQL As String
    Dim rs As DAO.Recordset
    Dim filRs As DAO.Recordset
    Dim delQdef As QueryDef
    Dim updateQdef As QueryDef
    Dim insertQdef As QueryDef
    Dim flIdQdef As QueryDef 'our prepared statments
    Dim uFl As New updateFloors 'if the floor doesn't exist we need to insert it before continuing
    Dim flRS As Recordset 'we need this for the result of the prepared statment
    
    Dim errBit As Boolean
    
    errBit = False
    
    'Everything that touches the DB should be a prepared statment
    Set delQdef = CurrentDb.QueryDefs("Delete_From_Seats")
    Set updateQdef = CurrentDb.QueryDefs("UpdateSeats")
    Set insertQdef = CurrentDb.QueryDefs("InsertSeats")
    Set flIdQdef = CurrentDb.QueryDefs("getFloorId")
    
    'Every file that we pull from
    
    SQL = "SELECT FName FROM Files ORDER BY Fname;"
    Set rs = CurrentDb.OpenRecordset(SQL)
    Do While rs.EOF = False
    
        fil = rs!FName
      
        SQL = "SELECT * FROM [" & fil & "];"
    
        Set filRs = CurrentDb.OpenRecordset(SQL)
        
        If Not filRs.EOF Then
            filRs.MoveFirst
        End If
        
        Do Until filRs.EOF
            
            'Check if the floor exists and if not call the floor to insert it
            flIdQdef.Parameters("fl") = filRs("Building and Floor #")
            
            Set flRS = flIdQdef.OpenRecordset
            
            If flRS.EOF Then
'                uFl.insertFloor (filRs("Building and Floor #")) 'This should recursivly add nonexistant buildings and sites
            End If
            
            flIdQdef.Parameters("fl") = filRs("Building and Floor #") 'This needs to be re set to make sure we have the new floor we inserted
            
            Set flRS = flIdQdef.OpenRecordset
            
            'Delete removed seats
      
            'All business logic should be on a different tier

'*** Excel Linked Data Fields ****      
            'Here we call the stored procedure to insert the data
            updateQdef.Parameters("s") = filRs("Seat Number")
            updateQdef.Parameters("a") = filRs("Calculated Area")
            updateQdef.Parameters("occ") = filRs("Occupancy")
            updateQdef.Parameters("cap") = filRs("Capacity")
            updateQdef.Parameters("fID") = flRS("ID")  
            '''updateQdef.Parameters("fID") = flRS("Floor")
            updateQdef.Parameters("ss") = filRs("Space Status")
            updateQdef.Parameters("sf") = filRs("Space Function")
            updateQdef.Parameters("st") = filRs("Space Type")
            updateQdef.Parameters("hd") = filRs("Home Department")
            updateQdef.Parameters("sn") = filRs("Space Name")
            updateQdef.Parameters("pres") = filRs("President")
            updateQdef.Parameters("vp") = filRs("Vice President")
            updateQdef.Parameters("dir") = filRs("Director")
            updateQdef.Parameters("ms") = filRs("Mail Stop")
            If Not InStr(fil, "Building7-01_Spaces") Then
           updateQdef.Parameters("mn") = filRs("Master Name")
            End If
            updateQdef.Parameters("shpnm") = filRs("Shape Name")
            updateQdef.Parameters("bf") = filRs("Building and Floor #")
            updateQdef.Parameters("sc") = ""
            
            insertQdef.Parameters("s") = filRs("Seat Number")
            insertQdef.Parameters("a") = filRs("Calculated Area")
            insertQdef.Parameters("occ") = filRs("Occupancy")
            insertQdef.Parameters("cap") = filRs("Capacity")
            insertQdef.Parameters("fID") = flRS("ID")
            '''insertQdef.Parameters("fID") = flRS("Floor")
            insertQdef.Parameters("ss") = filRs("Space Status")
            insertQdef.Parameters("sf") = filRs("Space Function")
            insertQdef.Parameters("st") = filRs("Space Type")
            insertQdef.Parameters("hd") = filRs("Home Department")
            insertQdef.Parameters("sn") = filRs("Space Name")
            insertQdef.Parameters("pres") = filRs("President")
            insertQdef.Parameters("vp") = filRs("Vice President")
            insertQdef.Parameters("dir") = filRs("Director")
            insertQdef.Parameters("ms") = filRs("Mail Stop")
            If Not InStr(fil, "Building7-01_Spaces") Then
            insertQdef.Parameters("mn") = filRs("Master Name")
            End If
            insertQdef.Parameters("shnm") = filRs("Shape Name")
            insertQdef.Parameters("bf") = filRs("Building and Floor #")
            insertQdef.Parameters("sc") = ""
            
            
            updateQdef.Execute
            
            'if no records get updated insert the new one
            If updateQdef.RecordsAffected = 0 Then
                insertQdef.Execute
            End If
            
            
            filRs.MoveNext
        Loop
    
        rs.MoveNext
    Loop
  
    filRs.Close
    Set filRS = Nothing
    rs.Close
    Set rs = Nothing

--------------------
Go to the top of the page
 
BruceM
post Dec 14 2016, 10:27 AM
Post#12


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


You answered my question about the nature of the data in the array. However, I don't often use querydefs, and never at this level of complexity, so I can't sort out what is going on. I'll put out a call for more eyes on this.

Edit: I see Doug is on this, so I'll step back and see what I can learn.
Go to the top of the page
 
SubPlanner
post Dec 14 2016, 10:28 AM
Post#13



Posts: 34
Joined: 22-April 16



Thanks for the response. I could tell by the code you posted yesterday that it would be better to put it directly in the UpdateSeats code.

Also, Will I still need to use the first part of the routine that I posted in the beginning of this thread?

I do a an error handler to put back in at the bottom.
Should I do this after your closing statements or before?

Thanks for your help, I will bolt this in and see how it acts.

SubPlanner
Go to the top of the page
 
SubPlanner
post Dec 14 2016, 12:52 PM
Post#14



Posts: 34
Joined: 22-April 16



I tested this out on just a few items and it seams to work.
The big test will come tonight when I test it on about 140 items.
This program usually takes about 2 hours to run anyway.

SubPlanner.
Go to the top of the page
 
SubPlanner
post Dec 15 2016, 09:22 AM
Post#15



Posts: 34
Joined: 22-April 16



I am getting an error here:
CODE
updateQdef.Execute
            
            'if no records get updated insert the new one
            If updateQdef.RecordsAffected = 0 Then
                insertQdef.Execute
            End If
            
            
            filRs.MoveNext


It bugs on the line insertQdef.Execute.

The error says:
Run Time Error '3061:'
Too few parameters. Expected 18.

I can't make heads or tails of where this is failing.

SubPlanner.

These are my current references: I may have an ADO or DAO situation.
Attached File(s)
Attached File  Ref.GIF ( 43.38K )Number of downloads: 0
 
Go to the top of the page
 
Doug Steele
post Dec 15 2016, 11:10 AM
Post#16


UtterAccess VIP
Posts: 21,490
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Whoa. That's an awful lot of references! Do you really need them all? (It's extremely rare for me to have more than 4 or 5)

Your issue is definitely not an ADO or DAO situation. What the error is saying is that insertQdef is expecting 18 parameters to have been defined, and they weren't. Not quite sure why not, since you have 18 lines of code setting parameters in updateQdef and 18 lines for insertQdef, and updateQDef worked successfully.

What exactly is your code supposed to be doing? Assuming your naming conventions are correct, I can see that you're looping through a recordset, setting parameters for both an update and insert query row-by-row from the recordset, running the update query, then running the insert query if the update query didn't do anything. That's extremely inefficient! It's seldom, if ever, appropriate to loop through a recordset, running a query against each row. Write a single SQL to do it all in one fell swoop! It's possible to do both updates and inserts at once: check Doing Two Things at Once (The sample database is available for free in the November, 2003 download at http://www.accessmvp.com/djsteele/SmartAccess.html)

--------------------
Go to the top of the page
 
SubPlanner
post Dec 15 2016, 12:27 PM
Post#17



Posts: 34
Joined: 22-April 16



Thanks for the response Doug. This was built by someone else and now it belongs to the only noob in the room who knows enough about Access db's to be dangerous.
I agree with you, I would not have built it this way.
I will look at the links you supplied and work on making this thing run.

I will get back to you with the details.

SubPlanner
Go to the top of the page
 
SubPlanner
post Dec 19 2016, 11:42 AM
Post#18



Posts: 34
Joined: 22-April 16



An update on this "Goat Roping" exercise.
I took your advice and totally rebuilt the way this db updates.
Basically instead of having an array, I just import all the external excel data into one table and run the update and append queries after that.
This is why I hate taking over other peoples databases.

Anyway, the db works fine now, thanks for all your help.
SubPlanner
Go to the top of the page
 
SubPlanner
post Jan 10 2017, 07:00 AM
Post#19



Posts: 34
Joined: 22-April 16



I have an extra step in this evolution that I need help with.

I need to loop through the table I created and open external Excel documents.
There are about 165 external Excel documents.
I have a form created in Access that allows users to pick what documents they want to update.
They could pick 1 or 2. Or they could pick 20 or 40 of them.

So what I think I need is a fancy loop code that would loop through a table of file names and file directories, then open the excel documents and save them.
The table is named "ExcelFiles" and 164 Excel document names inside the table are in the column named FName, and a file directory location named FPath.

Thanks for any help you can offer
SubPlanner.
Go to the top of the page
 
BruceM
post Jan 10 2017, 07:45 AM
Post#20


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


I don't know how you are using the form to allow users to select the files, but if it is a multi-select list box perhaps this code could be adapted. Rather than building a filter string you may be able to process each file as needed for each item selected.

BTW, you may do better to start a new topic. Addenda to old topics tend not to get as much attention as new postings.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 01:39 AM