Connection string

[KenHigg]

Ok... I've done this in the past and I scanned the threads and I'm still hung up. I have a sql server data source, etc and I want to connect to it via code. I know I use the connect method of something.

SERVER=10.65.203.28
UID=myusers
PWD=myuserspw
DATABASE=My-parts

What the heck is the code to have it linked / re-linked when the db is opened so that a new user can access the tables?

 

[odin1701]

Something like this - though you'd have to re-link tables - and on exit you should remove them.

Set sqlConn = New ADODB.Connection

sqlConn.Provider = "sqloledb"
sqlConn.Properties("Data Source").Value = SERVER
sqlConn.Properties("Initial Catalog").Value = DATABASE
sqlConn.Properties("User ID").Value = UID
sqlConn.Properties("Password").Value = PWD

I also use DSN when linking tables....

Just use notepad and make a file like this:

[ODBC]
DRIVER=SQL Server
UID=Insert ID Here
Trusted_Connection=Yes
WSID=Insert Workstation ID here (if needed)
APP=Microsoft Data Access Components
SERVER=Put IP Address Here
Description=Name It

save it as a .dsn file.

Then to link a table:

DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DRIVER=SQL Server;DSN=Path to DSN file here;DATABASE=DB Name on server;WSID=Workstation, if needed;APP=Microsoft Open Database Connectivity;SERVER=Server name (not IP);", acTable, "table name (on server)", "table name (local)"


I think I labeled that all right.

 

[odin1701]

Oh and you can skip the DSN but put UID and PWD in the code too. Anyway hopefully this will point you in the right direction.

 

[KenHigg]

Thanks odin, I got it workin with the following code:

    Dim DB As Database
    Dim TD As TableDef
    
    Dim strConnect As String

    strConnect = "ODBC;Driver={SQL Server};Server=10.65.203.28;Database=MyParts;Uid=myuser;Pwd=mypassword;"

    Set DB = CurrentDb()
    Set TD = DB.CreateTableDef("MyNewTable")
    
    TD.Connect = strConnect
    TD.SourceTableName = "MySqlTableName"
    
    DB.TableDefs.Append TD

 

[odin1701]

Yeah they have workstation restrictions and a bunch of crap on the SQL server here. It's annoying.