Pass-through query parameter for SQL 2008 database

Question

Is there a way to change the SQL database named in the FROM statement, and if so, how?  Sometimes there is a need to query a different SQL 2008 database and rather than change each query individually, it would be very helpful if a parameter could be used from a form or some other means. 

This works in the query - FROM [TheDB].[dbo].tblname

I tried using a control on a form, because sometimes the query needs to look at a different/backup database, but keep getting error 102. 

This does not work in the query - FROM [Forms]![frmStartup]![dbLocation].[dbo].tblname

          I also tried with parenthises as: ([Forms]![frmStartup]![dbLocation]).[dbo].tblname

It is confusing because you can refer to a control on a form in the WHERE clause and it works. 

Any suggestions would be greatly appreciated.

I should also say that I am using Access 2000 on a WindowsXP machine and have many pass-thru queries that work just fine, but in order to look at a different/backup SQL server it is necessary to change each query individually.

Thanks very much.

Answer 1

Hi,

BTW this thread has been moved to the Microsoft Community - Office - Access

Forums where there should be some help.

Microsoft Community - Office - Access Forums

http://answers.microsoft.com/en-us/office/forum/access?tm=1349475309211

Hope this helps.

---

Rob Brown - Microsoft MVP <- profile - Windows Expert - Consumer : Bicycle <- Mark Twain said it right!

Answer 2

No, you cannot use a parameter to change the database in an Access query. For that matter, you cannot use parameters in pass-through queries: pass-through queries run on the server, so they know nothing at all about anything on the client.

Your only choice would be to dynamically regenerate the SQL, and change the SQL associated with the query to the new SQL before running it.

Answer 3

Hi,

SQL Server and other Windows Server versions are supported via

TechNet so best to check with the Experts there.

TechNet - SQL Server

http://social.technet.microsoft.com/Forums/en/category/sqlserver

TechNet - Windows Server (Server 2008 R2 Forums are locked so

you will need to use the other forums for current support)

http://social.technet.microsoft.com/Forums/en/category/windowsserver

TechNet - Xp, Vista, Windows 7, and Windows 8 IT Pro

http://social.technet.microsoft.com/Forums/en/category/w7itpro,w8itpro,windowsvistaitpro,windowsxpitpro,windowsintune

TechNet- Forums

http://social.technet.microsoft.com/Forums/en/categories/

TechNet - Where is the Forum For…?

http://social.technet.microsoft.com/Forums/en/whatforum/threads

Hope this helps.

---

Rob Brown - Microsoft MVP <- profile - Windows Expert - Consumer : Bicycle <- Mark Twain said it right!