Setting connection string to linked table programmaticaly ?

  • Thread starter Thread starter Zlatko Matiæ
  • Start date Start date
Z

Zlatko Matiæ

Hello.
If I want to use DSN-less instead of File DSN, do I need to recreate linked
tables on each client, or I can set connection string programmaticcaly ? If
so, how?
It is easy with pass-through queries, but, what about linked tables ?

Also, how can I change and create fake indexes on linked table ?

Zlatko
 
This is the code I use to change the Connection String of ODBC-linked Tables
(in my case SQL Server 2000 back-end). I probably copied from somewhere and
modified so unknown author is hereby acknowledged.

You probably need to modify it to suit your needs:

********
' Needs DAO Library in the References
Function FixConnStr_ODBCTables() As Boolean

Dim db As DAO.Database
Dim tdf As DAO.TableDef

On Error GoTo FixConnStr_ODBCTables_Err
Set db = DBEngine(0)(0)

For Each tdf In db.TableDefs
If Eval("'" & tdf.Connect & "'" & " Like 'ODBC*'") = True Then
tdf.Connect = gMSSQL_ODBC
tdf.RefreshLink
End If
Next

FixConnStr_ODBCTables = True
MsgBox "Connection String for all ODBC-linked Tables are updated."

FixConnStr_ODBCTables_Exit:
Set tdf = Nothing
Set db = Nothing
Exit Function

FixConnStr_ODBCTables_Err:
Select Case Err.Number
Case 0
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf &
vbCrLf & _
"(Programmer's note: vbaFixServerConnection.FixConnStr_ODBCTables)"
& vbCrLf, _
vbOKOnly + vbCritical, "Run-time Error!"
End Select
FixConnStr_ODBCTables = False
Resume FixConnStr_ODBCTables_Exit
End Function
********

gMSSQL_ODBC is a Public Constant stored in a Module dedicated to constants &
with value like:

Public Const gMSSQL_ODBC = "ODBC;DRIVER=SQL Server;" & _
"SERVER=MySQLServer;APP=Microsoft Office XP;" & _
"DATABASE=MyDatabase;Network=DBMSSOCN;" & _
"Address=MySQLServer,PortNo;Trusted_Connection=Yes;Regional=Yes;"

The above String uses TCP/IP as the Network Transmission Protocol which you
need to change if you use Named Pipe. Also MySQLServer, MyDatabase and
PortNo are specific to my set-up and you need to change these for your
set-up. PortNo is usually 1433 but it depends on the person who set the SQL
Server up (I saw one with PortNo 1444).
 
Thank you very much!
Your code works.

But, I have problems with "#Deleted" and primary key...Now I'm trying to
solve it by making composite primary keys on server...Seems to be tricky...

Bye
 
Hi.
Thank you.

Van T. Dinh said:
This is the code I use to change the Connection String of ODBC-linked
Tables
(in my case SQL Server 2000 back-end). I probably copied from somewhere
and
modified so unknown author is hereby acknowledged.

You probably need to modify it to suit your needs:

********
' Needs DAO Library in the References
Function FixConnStr_ODBCTables() As Boolean

Dim db As DAO.Database
Dim tdf As DAO.TableDef

On Error GoTo FixConnStr_ODBCTables_Err
Set db = DBEngine(0)(0)

For Each tdf In db.TableDefs
If Eval("'" & tdf.Connect & "'" & " Like 'ODBC*'") = True Then
tdf.Connect = gMSSQL_ODBC
tdf.RefreshLink
End If
Next

FixConnStr_ODBCTables = True
MsgBox "Connection String for all ODBC-linked Tables are updated."

FixConnStr_ODBCTables_Exit:
Set tdf = Nothing
Set db = Nothing
Exit Function

FixConnStr_ODBCTables_Err:
Select Case Err.Number
Case 0
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf &
vbCrLf & _
"(Programmer's note: vbaFixServerConnection.FixConnStr_ODBCTables)"
& vbCrLf, _
vbOKOnly + vbCritical, "Run-time Error!"
End Select
FixConnStr_ODBCTables = False
Resume FixConnStr_ODBCTables_Exit
End Function
********

gMSSQL_ODBC is a Public Constant stored in a Module dedicated to constants
&
with value like:

Public Const gMSSQL_ODBC = "ODBC;DRIVER=SQL Server;" & _
"SERVER=MySQLServer;APP=Microsoft Office XP;" & _
"DATABASE=MyDatabase;Network=DBMSSOCN;" & _
"Address=MySQLServer,PortNo;Trusted_Connection=Yes;Regional=Yes;"

The above String uses TCP/IP as the Network Transmission Protocol which
you
need to change if you use Named Pipe. Also MySQLServer, MyDatabase and
PortNo are specific to my set-up and you need to change these for your
set-up. PortNo is usually 1433 but it depends on the person who set the
SQL
Server up (I saw one with PortNo 1444).
Click to expand...
 
Back
Top