MS Access SQL Server Table Connection

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.
Who is Participating?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.

Pawan KumarDatabase ExpertCommented:
You can do like below

Pass the name of the table at runtime

Exec('' select columnname from ' + variabletablename + '')
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.