Looking for file...

[cranebill]

I copied and renamed an access file so I have a way to play with making new reports etc.

I am playing around with a loop as seen below however I get an error:

Run-Time error '3024'

Cannot find file.... the original file name and loacation

How do you change the reference to look in the new location and name?

Private Sub Command0_Click()
Dim objRecordset As Recordset
Dim lstUsers

Set objRecordset = CurrentDb.OpenRecordset("select Name, count(*) as QTY from qa.task_list a, qa.req_desc b, qa.employee_list c where a.PROJECT_NUMBER = b.PROJECT_NUM and a.tester = c.userid and job_num = 9595 group by name order by count(*) desc")
Do While Not objRecordset.EOF
lst_users = lst_users & objRecordset.Fields("Name") & Chr(10) & Chr(13)
objRecordset.MoveNext
Loop

MsgBox lst_users
End Sub

 

[PHV]

No linked tables you should relink ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

This Set objRecordset = CurrentDb is the new location.

Name is a very bad name indeed for a field. You must enclose it in braces: [Name], or better yet, rename it. Avoid using reserved words.

 

[cranebill]

All tables are ODBC connected... so just delete and relink...

all other forms etc used work fine, just not when I use CurrentDb.OpenRecordset I am assuming...

I copied this db to the original location and renamed it back to the old file and it works...
hmmmm

 

[OhioSteve]

I don't have a silver bullet answer, but I can give some advice on this issue.

Whenever I open a recordset from the current .mdb, I use a connection string. It is kinda weird because the .mdb is contacting itself. I do not know whether everyone uses this approach, or just me. At times I have had problems because I forgot to change the connection string when I moved the .mdb. Then I got error messages exactly like yours.

To solve this problem, I began using CurrentProject.FullName. This method returns a string like "C:\salesDB\acmeSales.mdb". I contatenated the fullname into my connection string.