Remote query problem

[Pyro]

Hi,

I am trying to run a remote query that looks in a database that is in the same directory as the Back End of my main database.

In VBA i can run that sql and connect to the database using a function:

SELECT...
FROM tablename
IN """ & MyDBPath & "MyDBName.accdb""
WHERE

However i cannot come up with a rendition of this that will work in the SQL of a regular query.

Any thoughts?

 

[HiTechCoach]

What is the value returned by MyDBPath ?

 

[Pyro]

MyDBPath returns the path to the linked Back End.

 

[HiTechCoach]

MyDBPath returns the path to the linked Back End.

I understood that already from your original post.

You still have not answered my original question. I wanted to now the actual value that is getting return into the SQL.

 

[Pyro]

If I declare my SQL as a string and then execute it using MyDBPath as in my original post, I have no problems.

What I am trying to do is use that same function within the SQL of a regular query to determine the path of my remote DB which can be used when the BE file has been moved. For the purpose of this, i am assuming that the database that i am trying to query remotely will always reside in the same directory as the BE of my main DB.

If I attempt to run the query as stated in my original post, I get syntax errors, if I convert all of the double quotes to single quotes - syntax errors...

If I run this query from a form, I can change the SQL before it opens using:

Dim dbsCurrent As Database
Dim qry_Secondary As QueryDef
 
Set dbsCurrent = CurrentDb
Set qry_Secondary = dbsCurrent.QueryDefs("[B]Query Name[/B]")
 
[COLOR=seagreen]'Change the source of the query[/COLOR]
qry_Secondary.SQL = "SELECT... " & _
                      "FROM tablename " & _
                      "IN """ & MyDBPath & "MyDBName.accdb"" " & _
                      "WHERE...;"

I guess I could even run that code every time the DB opens, but I am just wondering if there is a more simple way around the issue.

So basically I want to just set the query up using some technique that will connect to the remote DB if it's location changes.

 

[namliam]

How about instead of using a query in a form, use SQL as your recordsource then change that?

me.recordsource = "SELECT... " & _
                          "FROM tablename " & _
                          "IN """ & MyDBPath & "MyDBName.accdb"" " & _
                          "WHERE...;"

Also why use an "IN" syntax anyways? why not "simply" use linked tables, you can update them to link to the proper database (search my name and link or something simular) and use a 'normal' query in this database?

 

[Pyro]

Also why use an "IN" syntax anyways? why not "simply" use linked tables, you can update them to link to the proper database (search my name and link or something simular) and use a 'normal' query in this database?

In this particular instance, i am running this query as part of a check before appending records into an archive database. The archive database unfortunately has tables with the same names and structure as the main DB, so i didn't want to confuse myself with table references.

Often i do use the method you have mentioned.

I thought that there might be a simple means by which to achieve what i have set out to do. Failing that, i can work with the code that i have.

Using the IN clause, IMO is not at all complex. If i can sort this out, it will certainly be no more difficult than automating the re-linking of tables from multiple DB's

 

[namliam]

The archive database unfortunately has tables with the same names and structure as the main DB, so i didn't want to confuse myself with table references.

Use a naming convention, i.e. The archive tables: ARCHtblName
Or something simular... no way to get confused... I think

Using the IN clause, IMO is not at all complex. If i can sort this out, it will certainly be no more difficult than automating the re-linking of tables from multiple DB's

Like I said I posted code for that on this forum, it automaticaly goes to find the DBs that are currently attached.