Upsized Access DB, DAO and ODBC

Question

Post reply

Upsized Access DB, DAO and ODBC

rschaeferhig

SSCrazy Eights

Points: 8776
More actions
March 4, 2005 at 9:09 am

#66512

I've been working on a project to upsize an Access DB to a SQL backend. I've had to leave a lot of the DAO code in place because the effort to convert it (i.e. Crosstab queries and data-bound forms and controls) exceeds the customer's budget.
Since I have to use DAO I have to (as far as I know) define an ODBC connection on every desktop that will use the application. The ODBC connection has to match the name used in the Linked Table definitions. Since we may be looking at a large number of users I Googled around and came up with a VBScript to add the ODBC connection. Unfortunately there is apparently no way to add the password using this method (is there?), so the user is prompted for an ODBC login when they open the application.
I found some code that can be run in Access to re-link the tables programmatically and provide userid and password information. This purportedly will eliminate the password prompt problem, but do I need to do this every time the db is opened, or just the first time for each new user? Anyone know?
Any light that anyone can shed on this would be greatly appreciated. If you have alternative solutions to rolling out ODBC connections that would be appreciated also. Our Infrastructure group uses Kixstart for scripting and I've seen some examples there that may apply...
Looking for opinions before I spend a couple of days messing around with this.
TIA

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sqlSherwin SSCrazy Points: 2884 More actions · Answer 1

I came across this yesterday. It allows you to create an ODBC connection without a DSN.

http://support.microsoft.com/default.aspx?scid=kb;en-us;892490

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 2

One really easy way you can do this is to create a File type DSN. THen when you link the tables, say you want to save the password. If you use a File DSN, you can move the Access db to other workstations without creating the DSN. The connection information becomes one of the table properties.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 3

Actually Newbie's ref to MS pointed to some code that could be used to convert a DSN based Linked Table to a DSN-less Linked Table. I copied the code to a module in my Access DB and ran it. It converted all the links to DSN-less links (full server name, DB, Table, Userid and PWD in each link). Seems to work pretty well.

I can post here if anyone's interested.

ian payne SSC Veteran Points: 276 More actions · Answer 4

Yes please, I owuld be interested as I have a similar project to do

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 5

paste the following two functions into a module, then in the Immediate window enter "? AttachDSNLessTable()" and press Enter. It may run for a while so be patient.

Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)

On Error GoTo AttachDSNLessTable_Err

Dim td As TableDef

Dim stConnect As String

For Each td In CurrentDb.TableDefs

If td.name = stLocalTableName Then

CurrentDb.TableDefs.Delete stLocalTableName

End If

Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)

CurrentDb.TableDefs.Append td

AttachDSNLessTable = True

Exit Function

AttachDSNLessTable_Err:

AttachDSNLessTable = False

MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function

Function RetrieveSQLServerUserTables()

Dim cnn As ADODB.Connection

Dim rst As ADODB.Recordset

Dim strConnectionString As String

Dim strSQL As String

Set cnn = New ADODB.Connection

strConnectionString = "Provider=SQLOLEDB.1;" & _

"Data Source=servername;" & _

"Initial Catalog=dbname;" & _

"User ID=sqluserid" & _

";Password=sqlpwd"

cnn.Open (strConnectionString)

' Retrieve names of all SQL Server user tables

strSQL = "SELECT name FROM sysobjects WHERE xtype = 'U' and name not like 'dt%' order by name"

Set rst = New ADODB.Recordset

rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly

'Debug.Print rst.GetString(adClipString, , ";")

rst.MoveFirst

While Not (rst.EOF)

Call AttachDSNLessTable(rst.Fields("Name"), rst.Fields("Name"), "sqlservername", "dbname", "sqluserid", "sqlpwd")

rst.MoveNext

Wend

rst.Close

Set rst = Nothing

cnn.Close

Set cnn = Nothing

End Function