Using a variable in Access 2000

[Del]

I want to use code for the row source of a combo box to fill its list with
tables from a different database. The following code works if I have the
unique path 'C:\MyFolder\MyFile.mdb' included.

SELECT MSysObjects.[Name]
FROM MSysObjects IN 'C:\MyFolder\MyFile.mdb'
WHERE (MSysObjects.[Type] = 1)
AND NOT ((MSysObjects.[Name] Like "MSys*"))
ORDER BY MSysObjects.[Name];

But when I replace the unique path with the variable strPath, Access sees
the path with a back slash and the variable on the end. I get the following
message: Could not find the file 'C:\MyFolder\MyFile.mdb\strPath'

What is the correct syntax to replace the unique path with a variable?

 

[Klatuu]

Would help if you post the code that isn't working so we can see what the
problem is.

 

[Marshall Barton]

I want to use code for the row source of a combo box to fill its list with
tables from a different database. The following code works if I have the
unique path 'C:\MyFolder\MyFile.mdb' included.

SELECT MSysObjects.[Name]
FROM MSysObjects IN 'C:\MyFolder\MyFile.mdb'
WHERE (MSysObjects.[Type] = 1)
AND NOT ((MSysObjects.[Name] Like "MSys*"))
ORDER BY MSysObjects.[Name];

But when I replace the unique path with the variable strPath, Access sees
the path with a back slash and the variable on the end. I get the following
message: Could not find the file 'C:\MyFolder\MyFile.mdb\strPath'

strSQL = "SELECT MSysObjects.[Name] " _
& "FROM MSysObjects " _
& "IN 'C:\MyFolder\" & strPath & "' " _
& "WHERE (MSysObjects.[Type] = 1) " _
& "AND NOT MSysObjects.[Name] Like "MSys*" " _
& "ORDER BY MSysObjects.[Name]"

 

[Del]

I put following in a module I call modVariables: Public strPath as String

I set strPath to the user entered path of the different database

I put the following code in the Row Source of a combo box to fill its list
with the tables in the different database. This code does not work.

SELECT MSysObjects.[Name]
FROM MSysObjects IN strPath
WHERE (MSysObjects.[Type] = 1)
AND NOT ((MSysObjects.[Name] Like "MSys*"))
ORDER BY MSysObjects.[Name];

If I replace the variable strPath with the actual path it works. Ref below:

SELECT MSysObjects.[Name]
FROM MSysObjects IN 'C:\MyFolder\MyFile.mdb'
WHERE (MSysObjects.[Type] = 1)
AND NOT ((MSysObjects.[Name] Like "MSys*"))
ORDER BY MSysObjects.[Name];

--
Thank you,
Del

Would help if you post the code that isn't working so we can see what the
problem is.
--
Dave Hargis, Microsoft Access MVP

I want to use code for the row source of a combo box to fill its list with
tables from a different database. The following code works if I have the
unique path 'C:\MyFolder\MyFile.mdb' included.

SELECT MSysObjects.[Name]
FROM MSysObjects IN 'C:\MyFolder\MyFile.mdb'
WHERE (MSysObjects.[Type] = 1)
AND NOT ((MSysObjects.[Name] Like "MSys*"))
ORDER BY MSysObjects.[Name];

But when I replace the unique path with the variable strPath, Access sees
the path with a back slash and the variable on the end. I get the following
message: Could not find the file 'C:\MyFolder\MyFile.mdb\strPath'

What is the correct syntax to replace the unique path with a variable?