MS Access SQL Server Table Connection

deer777 used Ask the Experts™
I need to connect to a SQL Server table from MS Access.  The table name in Access is 123 but the SQL Server table name is 123_v_username.  I need the table name (123) to stay the same in Access but only change the connection string from SQL Server to change to 123_v_username.  How would I write the code to do this.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

You can do like below

Pass the name of the table at runtime

Exec('' select columnname from ' + variabletablename + '')
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
You'd want to modify the tabledef's connect property.

 In the VBA editor, call up the debug window (Ctrl/G).  Type:

? CurrentDB().tabledefs("123").Connect

and hit return.   You'll see the server, database, table name, etc.   Since this is a string, you can use Instr() to locate what you want to change, Left$(), Mid$(), etc to pick out the parts, build a new string, and update the connect property with that.


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial