Access 2013 Loop Through Recordset

[xljunkie]

I keep getting error messages when using

Dim rs as DAO.Recordset
Set rs = Currentdb.OpenRecordset("tblClients")
rs.MoveFirst
Do Until rs.EOF = True
     'Code
Loop
Set rs = Nothing

I assume this is because I'm on Access 2013 and in my references Microsoft DAO 3.6 is unchecked, and when I check it, I get an error in loading the DLL. What is the solution to this issue? Do I need to save the db as an .mdb rather than my current .accdb format? I wanted to keep the accdb format in order to have nicer looking forms/graphics, but it's not needed.

 

[Joe4]

For one thing, you are missing a MoveNext statement (see: https://msdn.microsoft.com/en-us/library/bb243789(v=office.12).aspx), but it sounds like you may also have other library issues going on there.

What exactly are you hoping to accomplish by looping through the table? There may be other ways of doing it.

 

[xljunkie]

Well I will need to run multiple processes. I have a table called tblClients which lists out a client number, name, etc. This list will grow over time so I am trying to find a way to automate some processes.

For one, I want to update other query criteria based on the number of clientid's found in tblClients. I've already found a solution to my original problem of creating a string of clientid's (1 or 2 or 3 or 4 ...) using a for loop. I added an autonumber to the table which I can use to dcount and use a for loop the specified number of times.

Anyway, my next problem is now updating query criteria using this string (1 or 2 or 3 or 4 ...). I'm seeing I can create a function which can be referenced within the query criteria, but I anticipate later on when I need to do some other processes, that having the ability to just loop through the records might be helpful.

 

[Norie]

Are you going to use a concatenated, delimited string of all client IDs as criteria in a query?

 

[xljunkie]

No, when you enter criteria in a query and you want to limit a dataset to a specific set of client ID's, you'd need to put a phrase such as this in the criteria field: 1 Or 2 Or 3 Or 4. So the criteria is not delimited. I've stored that particular string in a VBA variable, and I believe I can use a public function to place that criteria in a query.

But later on, I will need to export individual Excel files for each client, run a separate Excel macro from Access, save and close the Excel file, and move on to the next client. I've done this process before using an .mdb database. I don't remember if I needed the DAO object library for it. I will need to check tomorrow.

Anyway, if I do need the DAO object library to accomplish all the Excel work from Access, I will work on converting my db into the .mdb format tomorrow. I was just hoping I could achieve everything I need to do from the Access 2013 .accdb version. I'm not an expert or MVP-level programmer, but I've created a number of lengthy, automated processes in the past.

 

[Micron]

I get an error in loading the DLL

You should let us know what the error number and message text is. To dimension a variable as a DAO recordset, you must have a reference to that library. If all else fails, you can try not specifying DAO and let Access figure it out (this is late binding). e.g. Dim rs as Recordset. The file extension is not your problem, so accdb should work fine.

creating a string of clientid's (1 or 2 or 3 or 4 ...) using a for loop

IN (1,2,3,4...) is how I would do it - if I were employing your method, but I would not rely on a count of id's to decide on how many times to loop. Autonumbers can get out of wack, especially in a shared db. What if the numbers ever become 1,2,3,6 (no 5 anymore, for example). Record deletion is not the only reason these numbers can lead you astray, so please don't focus on that. A better method is to open a recordset on a query object or on a vba sql statment and let that govern how many times to loop. Also, there is something to be said for using a function to return a sql statement if it makes sense.

For one thing, you are missing a MoveNext statement

It's probably in the 'Code section you omitted?
some minor code suggestions:

Dim rs as DAO.Recordset 
Set rs = Currentdb.OpenRecordset("tblClients") 
If rs.Recordcount > 0 Then 'error will occur if 0 recs and you try to move first
 rs.MoveFirst 
 Do Until rs.EOF = True '(or Do While Not rs.EOF
 'Code Loop 
End If
rs.Close 'best to close the recordset before setting it to nothing.
Set rs = Nothing

 

[xljunkie]

I've tried many variations of leaving in DAO and not earlier today. I got various errors, which I'll investigate more tomorrow.

As far as using the IN statement, I use that all the time in SQL Server. I've found that most statements that are easy to accomplish and write in SQL Server are difficult to reproduce in Access. I didn't even know you could put IN in the criteria, but I'll try that out tomorrow. But again, looping through the recordset would help in not relying on the autonumber, and help me write a comma-delimited string.

 

[Micron]

I got various errors, which I'll investigate more tomorrow.

We'll be happy to try to help with those if you need it. In fact, we might have clued in on those if we could have seen the rest of your code. I suspect you know that if your string is text, you will have to incorporate quotes in that build. It might look like this air code

strWherePart = " WHERE ClientName IN ('"
[I]code stuff
Do Until or Do While Not part[/I]
strWherePart = strWherePart & rs.fields(numberhere) & "', '"
Move Next
strWherePart = Mid(strWherePart,1, Len(strWherePart)-3) & ") "

Sometimes to keep code more concise, I will pass rs.Fields(number) to a function that has the sql building portion.

 

[Norie]

Have you considered using sub queries?

 

[Joe4]

For one thing, you are missing a MoveNext statement

It's probably in the 'Code section you omitted?

It can be dangerous to make such assumptions! Especially when things aren't working as intended...