DSNless linked tables

[greenguy]

Howdy, I'm self taught here so if I'm asking a dumb question I apologize. I've created an Access FE with a MSSQL BE. I don't want to have to create an ODBC connection for all the users (lazy I guess) so I found some code on the web that will allow me to not have to have odbc linked tables, modified it a bit and it seems to work fine. I created a table containing all the names of the tables I have in MSSQL, loop through those and create/refresh the tables. Please advise if I should pursue other measures.

Thanks

Public Sub CreateLinkedTable()

'Used to hold tabledef object and connection string
Dim td As TableDef
Dim ConnString As String
Dim LocalTableName As String
Dim SourceTableName As String
Dim SourceServerName As String
Dim SourceDatabaseName As String
Dim Login As String
Dim SourcePassword As String
Dim rs As DAO.Recordset
Dim db As Database

Set db = CurrentDb

strSQL = "SELECT [TableName] FROM tblnetworktables;"
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst

Do Until rs.EOF

'Sets all variable values for connection string
LocalTableName = rs(0).value
SourceTableName = rs(0).value
SourceServerName = "xxxxxxxxx"
SourceDatabaseName = "xxx"
Login = "xxxxxxxxx"
SourcePassword = "xxxxxxxxxxx"

'Creates connection string for the linked table
ConnString = "ODBC;DRIVER=SQL Server;SERVER=" & SourceServerName & ";DATABASE=" & SourceDatabaseName & ";UID=" & Login & ";PWD=" & SourcePassword

'Sets the table definition object
Set td = CurrentDb.CreateTableDef(LocalTableName, dbAttachSavePWD, SourceTableName, ConnString)

'Creates/refreshes the table by appending to the current list of table definitions.
On Error Resume Next
CurrentDb.TableDefs.Append td
CurrentDb.TableDefs.Refresh
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub

 

[Banana]

The only concern is that you are missing out generating an unique index necessary to make some table updateable as Access doesn't always auto-figure it out.

Take a look at Doug Steele's code (it's posted on his website and you can google for it).

HTH.

 

[pbaldy]

I'd point out that as an alternative, you can also use a File DSN on the server to link tables, which avoids having to go out to each client PC and create a User or System DSN.

 

[greenguy]

Thanks guys. I'll have to do some research as I'm not 100% sure what's being said but I'll look into it.

 

[DCrake]

Also the connection strings need to be outside of the DO WHILE ... Loop routine as they only need to be declared once.

SourceServerName = "xxxxxxxxx"
SourceDatabaseName = "xxx"
Login = "xxxxxxxxx"
SourcePassword = "xxxxxxxxxxx"

Also you are declaring the recordset as a DAO but not the CurrentDb

 

[greenguy]

Thanks for the info guys! DCrake, I'm not sure what you mean by not declaring the CurrentDb. Again I'm self taught so my appologies for beeing ignorant on this. I still haven't reseached the unique index Banana was refering to but I'll look into it. Below is what I have after making some of the changes recommended.

Public Sub CreateLinkedTable()
'Used to hold tabledef object and connection string
Dim td As TableDef
Dim ConnString As String
Dim LocalTableName As String
Dim SourceTableName As String
Dim SourceServerName As String
Dim SourceDatabaseName As String
Dim Login As String
Dim SourcePassword As String
Dim rs As DAO.Recordset
Dim db As Database
Set db = CurrentDb
SourceServerName = "xxxxxxx"
SourceDatabaseName = "xxx"
Login = "mdcwriter"
SourcePassword = "xxxxxxxxx"
'Creates connection string for the linked table
ConnString = "ODBC;DRIVER=SQL Server;SERVER=" & SourceServerName & ";DATABASE=" & SourceDatabaseName & ";UID=" & Login & ";PWD=" & SourcePassword
strSQL = "SELECT [TableName] FROM tblnetworktables;"
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst
Do Until rs.EOF
'Sets all variable values for connection string
LocalTableName = rs(0).value
SourceTableName = rs(0).value

'Sets the table definition object
Set td = CurrentDb.CreateTableDef(LocalTableName, dbAttachSavePWD, SourceTableName, ConnString)
'Creates the table by appending to the current list of table definitions.
On Error Resume Next
CurrentDb.TableDefs.Append td
CurrentDb.TableDefs.Refresh
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub

 

[DCrake]

In your earlier post you had the following INSIDE the Do While Loop so each loop it set the values again and again. As you only needed to declare them one it was practicable to set them OUTSIDE of the loop.

SourceServerName = "xxxxxxx"
SourceDatabaseName = "xxx"
Login = "mdcwriter"
SourcePassword = "xxxxxxxxx"

 

[greenguy]

Yep think I took care of that just wondering what you meant by "Also you are declaring the recordset as a DAO but not the CurrentDb". Thanks for the reply