Linking to SQL Server tables from Access without using DSN

[Leighton.d]

I'm struggling with putting together the code that will allow me to create
links to a few tables on a SQL server from Access. I can do it using a DSN,
but I'd much rather establish the connection without a DSN. I've been
retrieving data from years without a DSN, I just haven't stumbled upon the
right combination of code to create the necessary links.

Dim Db As Database
Set Db = CurrentDb()
Dim tdfLinked As TableDef
tdflinked.connect = "Driver=SQL Server; server=sql01.hccanet.priv;
database=DASLr; UID=" & Me.txtSqlUsername & ";PWD=" & Me.txtSqlPassword & ";"
tdfLinked.SourceTableName = strSourceTableName
Db.TableDefs.Append tdfLinked

I'm not able to get it to work.

Any ideas?

Thanks!

 

[Leighton.d]

Here's my code in its' current form:

Dim Db As Database
Set Db = CurrentDb()
Dim tdfLinked As TableDef
Set tdfLinked = Db.CreateTableDef("dbo_tblUserViewAccess")
tdfLinked.Connect = "Driver=SQL Server; server=sql01.hccanet.priv;
database=DASLr; UID=" & _
Me.txtSqlUsername & ";PWD=" & Me.txtSqlPassword & ";"
tdfLinked.SourceTableName = strSourceTableName
Db.TableDefs.Append tdfLinked

When it hits the last line, I receive a "Could not find installable ISAM"
error message. I know this can be done, I just haven't hit upon the write
combination!

Thanks...

 

[Ron Weiner]

Something like this ought to get it done.

DoCmd.TransferDatabase acLink, "ODBC", strCon, acTable, SourceTableName,
DestTableName, , True

Where strConn is a valid connection string for your server. Might look
something like

strCon = "ODBC;" _
& "Description=DSNLessConnection;" _
& "DRIVER=SQL Server;" _
& "SERVER= sql01.hccanet.priv;" _
& "UID==" & Me.txtSqlUsername & ";" _
& "PWD==" & Me.txtSqlPassword & ";" _
& "DATABASE=DASLr"

 

[Leighton.d]

I had to tweak you code a tiny bit. It didn't like the double "==" in the
code. Here's what I ended up with that works like a charm:

strCon = "ODBC;" _
& "Description=DSNLessConnection;" _
& "DRIVER=SQL Server;" _
& "SERVER=sql01.hccanet.priv;" _
& "UID=" & Me.txtSqlUsername & ";" _
& "PWD=" & Me.txtSqlPassword & ";" _
& "DATABASE=DASLr;"

Thanks for your assistance!

 

[Ron Weiner]

Sorry about the double equal's, I was careless when I pasted in the UID and
PWD expressions from your sample. I really should spend a little time
reviewing what I have written before pushing the send button.

Glad it worked out.