Where's the Connections String? (1 Viewer)

[kwilson090507]

I'm new to this forum, so if this has been discussed in the past, I apologize for the re-post.

I am using Microsoft Access 2003 with SQL Server 2005 Express Edition. In the past, I just create the tables in Access and then use the upsizing wizard to create the connection to the SQL server. What I can't figure out is where the connection string is located in the Access database. It can't be local, because I share the database with other users and don't have to create ODBC connections on their PC's to connect.

This became a problem recently when I moved information to a new server. I had to re-create the upsizing because I couldn't find where to change the connection paramaters. Any help would be greatly appreciated.

 

[WayneRyan]

K,

The connection resides in the table MSysObjects. You can't alter the table
directly.

If you just want to dynamically refresh the link to a table, you use the
TableDefs object.

I can get you an example if you want.

Wayne

 

[WayneRyan]

KWilson,

Been thinking about my last response. Not sure exactly what you need to
do, but this code fragment should help:

Dim tdf As DAO.TableDef    ' <- For Access, you set the link by using the TableDef
'                               The connection "resides" in Access in the mSysObjects table.
'                               You can view it, but need to use TableDef to change it.
Dim dbs As DAO.Database
'
Dim DbConnection As ADODB.Connection  <- 2nd topic is a DSN-less connection to SQL Server
Dim strConnection As String

Set dbs = CurrentDb

'
' 1st, re-establish the SQL Server link for an Access table.
' Access Name       --> LinkedAccessTableWithAccessName
' Server table name --> SQLServersTableName
' It is on server   --> YourSqlServer
' It is in database --> WhichSqlServerDatabase
' It uses a trusted connection
'
Set tdf = dbs.TableDefs("LinkedAccessTableWithAccessName")  <-- Select Access Table
tdf.Connect = "Driver={SQL Server};" & _                    <-- Point it to SQL Server
              "Server=YourSqlServer;" & _
              "Database=WhichSqlServerDatabase;" & _
              "Trusted_Connection=Yes;" & _
              "Table=SQLServersTableName"                  
tdf.RefreshLink                                             <-- Refresh it
Set tdf = Nothing
'
' Now, as a 2nd topic, set an ADODB connection to some database
' on your SQL server.
'
strConnection = "Driver={SQL Server};" & _
                "Server=YourSqlServer;" & _
                "Database=WhichSqlServerDatabase;" & _
                "TrustedConnection=Yes;"

Set DbConnection = New ADODB.Connection
  With DbConnection
    .CursorLocation = adUseClient
    .Mode = adModeReadWrite
    .Properties("Prompt") = adUseClient
    Call .Open(strConnection)
    End With

hth,
Wayne

 

[kwilson090507]

Wayne,

Thanks for the response. I found the connection information in the mSysObjects just like you said. Now, I'm trying to figure out the second part. For the most part, I do understand the code, but don't know where the TableDefs object is. Can you tell me how to access that so I can change the code?

Just FYI; the reason this came up is because I want to transfer all the databases from one SQL Server to another. The transfer is easy, but telling Access where to point to now has been a pain!

 

[WayneRyan]

KW,

The TableDefs collection is Access' way of storing/modifying the information
about the tables in your database. You can view "some" of the info in mSysObjects,
but you need to use the TableDefs to alter it.

You can drop the linked tables.
Then use the Linked Table Manager --> Files --> Linked Tables ...

Or, you can put the above code in your main form's OnOpen event and relink the
tables whenever your DB opens. It will make life easier, if the table designs
change etc.

hth,
Wayne

 

[WayneRyan]

KW,

The TableDefs collection is Access' way of storing/modifying the information
about the tables in your database. You can view "some" of the info in mSysObjects,
but you need to use the TableDefs to alter it.

You can drop the linked tables.
Then use the Linked Table Manager --> Files --> Linked Tables ...

Or, you can put the above code in your main form's OnOpen event and relink the
tables whenever your DB opens. It will make life easier, if the table designs
change etc.

hth,
Wayne