Code to link to a SQL table

[Robert_L_Ross]

OK, might be a weird question, but here goes.

We've noticed when we install a specific database with links to SQL tables
that the database runs extremely slow - unless we delete the SQL table links
and re-link.

Now, we don't have time/manpower to troubleshoot the problem, but since we
have a (relatively) quick solution, I'd like to try to set up code to do it.

I basically want to set up a form with a button that will delete the current
linked SQL tables, re-link (using ODBC) and re-name the tables (dropping the
dbo_).

Does anyone have any idea how I do this?

THX!

 

[Douglas J Steele]

Using a DSN, check http://www.mvps.org/access/tables/tbl0010.htm at "The
Access Web".

If you want to do it DSN-less, check
http://www.accessmvp.com/djsteele/DSNLessLinks.html at my site.

 

[Robert_L_Ross]

THX for the posting, but my TSS group will go ballistic if we even think of
looking at our Registry Keys.

I'm looking for commands within VBA or MSAccess to link to an existing ODBC
data source.

 

[Douglas J. Steele]

I believe that the only reason Dev's code looks at the registry is to ensure
that the DSN passed actually exists on the machine.

You should be able to strip all of the registry stuff out of that example.

 

[Robert_L_Ross]

Doug,

I think I have it down now, but I need to get some environment variables.
This code works:
DoCmd.TransferDatabase acLink, "ODBC", "ODBC;DRIVER=SQL
Server;SERVER=NTSQLQA;UID=UIDNAME;PWD=PASSWORD;APP=Microsoft Office
XP;WSID=WORKSTATIONID;DATABASE=OGS_0;NETWORK=DBMSSOCN;TABLE=dbo.BatchControl", , "dbo.BatchControl", "BatchControlNew", False

I need to know how to call and return my Workstation ID. When I install a
new ODBC Link, the driver knows what my Workstation ID is, so it's got to be
stored somewhere that I can call to. If I can get that, then this will work
like a charm.

THX for helping me thus far...if you know how to do this, you are the man!

 

[Robert_L_Ross]

Nevermind, I don't need the Workstation ID.

Now, here's the kicker:
I put the following into a macro:
Action:
TransferDatabase

Transfer Type:
Link

Database Type:
ODBC Database

Database Name:
ODBC;Driver=SQL
Server;Server=NTSQLQA;UID=ogs_user;PWD=ogs_user;APP=Microsoft Office
XP;WSID=;Database=OGS_1;NETWORK=DBMSSOCN;TABLE=dbo.BatchControl

Object Type:
Blank

Source:
dbo.BatchWork

Destination:
BatchWork

Structure Only:
No

Now, as a macro, it works great. Links to the SQL database just fine. I
don't need a Workstation ID 'cause it finds it on its own somehow (I tried
telling it the WSID was 99991D and it overrode it with the correct one).

So, I save this macro, then do a Save As, Module to get it to convert into
VB. When I save the macro as a module, this is the code it creates:
DoCmd.TransferDatabase acLink, "ODBC", "ODBC;Driver=SQL
Server;Server=NTSQLQA;UID=ogs_user;PWD=ogs_user;APP=Microsoft Office
XP;WSID=;Database=OGS_1;NETWORK=DBMSSOCN;TABLE=dbo.BatchControl", ,
"dbo.BatchWork", "BatchWork", False

Now, when I run the Macro, it works fine. When I try to put this VB code
into the Click Action of a Button, I get the following error:
"The expression On Click you entered as the event property setting produced
the following error: Duplicate Option Statement.
* The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
* There may have been an error evaluating the function, event or macro.

Do you have any idea what is missing or incorrect in the code? As a macro
it works great, but who wants to leave this as a macro? If at all possible,
I'd like to be able to call on this as a sub or function by using a button.

THX again for all of your help!

 

[Douglas J. Steele]

Paste the code from your Module. What name did you call the module?