Hi Russell,
Get your connection string information from the very useful
<http://www.connectionstrings.com/default.aspx> site. Click on the DSN link
under Miscellaneous for using a DSN. For ODBC connections without a DSN
(DSNless connections) choose the individual database/data file section and
then look for the part that tells about the database/data file's ODBC driver.
Clifford Bass
"Christian Coppes" wrote:
> Hi Russell,
>
> try it with this one:
>
> An advantage of this method is that you don't need an ODBC setting on the
> local
> computer, it's all in the source. Disadvantage is only that you need to save
> the password inside of the source, but you could use an input dialog if you
> want.
>
> Here's the code (the first function is from a MS knowledgebase article, see
> the comments. "fnErr" is a function which handles the errors in my
> application, exchange it with an own one. The second function uses the first
> one to link all the needed tables into Access. This works only in ACCDB, not
> in an ADP, reason shoule be clear...):
>
> '//Name : AttachDSNLessTable
> '//Purpose : Create a linked table to SQL Server without using a DSN
> '//Parameters
> '// stLocalTableName: Name of the table that you are creating in the
> current database
> '// stRemoteTableName: Name of the table that you are linking to on the
> SQL Server database
> '// stServer: Name of the SQL Server that you are linking to
> '// stDatabase: Name of the SQL Server database that you are linking to
> '// stUsername: Name of the SQL Server user who can connect to SQL
> Server, leave blank to use a Trusted Connection
> '// stPassword: SQL Server user password
> 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 fld As Field
> Dim stConnect As String
>
> For Each td In CurrentDb.TableDefs
> If td.Name = stLocalTableName Then
> CurrentDb.TableDefs.Delete stLocalTableName
> End If
> Next
>
> If Len(stUsername) = 0 Then
> '//Use trusted authentication if stUsername is not supplied.
> stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer &
> ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
> Else
> '//WARNING: This will save the username and the password with the
> linked table information.
> stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer &
> ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
> End If
> Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD,
> stRemoteTableName, stConnect)
> CurrentDb.TableDefs.Append td
> AttachDSNLessTable = True
> Exit Function
>
> AttachDSNLessTable_Err:
>
> AttachDSNLessTable = False
> fnErr "modODBC.AttachDSNLessTable"
> End Function
>
> ' Function to refresh every linked table
> ' Date: 17.06.08, Coppes
> ' Usage: The first array contains the table names used in Access
> ' The second array contains the table names used in SQL Server
> ' As the structure of linked tables are not automatically refreshed
> after
> ' changing in SQL Server this function also helps to have the most
> actual structure.
> '
> ' The DSN-less linking avoids the login screen to SQL server the first time
> a linked table
> ' is used. Moreover you don't need to install any DSN on the user's machine
> '
> ' The sub is not used in the application and must be started in the
> "Immediate" window in the VBA editor
> '
> Public Sub RefreshLinkedTables()
> ' Change the number of linked tables here if you add or delete tables
> Const constNumberOfTables = 3
> Dim strAccTables() As String
> Dim strSQLTables() As String
> Dim i As Long
>
> ReDim strAccTables(constNumberOfTables)
> ReDim strSQLTables(constNumberOfTables)
>
> ' These are only examples, this are the table names in the way they will
> appear in the tables list in Access
> strAccTables(0) = "tblCustomerSupplier"
> strAccTables(1) = "tblCustomerSupplierContact"
> strAccTables(2) = "tblBuild"
> strAccTables(3) = "tblDelivery"
>
> ' These are the table names in the way they appear in the SQL Server.
> strSQLTables(0) = "dbo.tblCustomerSupplier"
> strSQLTables(1) = "dbo.tblCustomerSupplierContact"
> strSQLTables(2) = "dbo.tblBuild"
> strSQLTables(3) = "dbo.tblDelivery"
>
> For i = 0 To constNumberOfTables
> AttachDSNLessTable strAccTables(i), strSQLTables(i),
> "EnterYourServerNameHere", "EnterTheNameOfTheSQLServerDatabaseHere",
> "EnterYourUserNameHere", "EnterYourPasswordForThisUserHere"
> Next
> End Sub
> ' -------------------------------
>
>
> As mentioned, you can exchange the literal password with an input box asking
> for the password to the server.
> So, if you know the names of the tables you need, you can change the values
> of the stringarrays above, change the constant of the number of tables you
> used (don't forget that an array begins with a 0), and call
> "RefreshLinkedTables", that's all.
>
> If the structure of the tables ever changes, you simply only need to call
> this function again and you're up to date.
>
> Cheers,
>
> Christian
|
Similar Threads
