DSN-Less Connection

[Trauton]

Hi,

I have an Access FE connected to a SQL server. Since I need to distribute
the FE to a number of users, I am trying to set up a dsn-less connection
using the following instructions:

The CreateTableDef method lets you create a linked table. To use this
method, create a new module, and then add the following AttachDSNLessTable
function to the new module.

'//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 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
MsgBox "AttachDSNLessTable encountered an unexpected error: " &
Err.Description
End Function

I have created the above (deleting all references to Local tables since I
have no Local tables), but I am having problems figuring out how to implement
the next set of instructions:

To call the AttachDSNLessTable function, add code that is similar to one of
the following code examples in the AutoExec macro:
• When you use the AutoExec macro, call the AttachDSNLessTable function, and
then pass parameters that are similar to the following from the RunCode
action.
AttachDSNLessTable ("authors", "authors", "(local)", "pubs", "", "")

I have added the RunCode Action to my autoexec macro calling the function
name as: AttachDSNLessTable («stRemoteTableName», «stServer», «stDatabase»,
«stUsername», «stPassword»)

When I run it I get an error message telling me: "DBName, can't find the
name: "<<stRemoteTableName>>" you entered in the expression.

What does this mean?

Is this the best way to accomplish what I'm trying to do? I'm not a
programmer (or very experienced with Access) so I need a little help
understanding this.

Thank you,

Trauton

 

[Douglas J. Steele]

To call the AttachDSNLessTable function, add code that is similar to one
of
the following code examples in the AutoExec macro:
. When you use the AutoExec macro, call the AttachDSNLessTable function,
and
then pass parameters that are similar to the following from the RunCode
action.
AttachDSNLessTable ("authors", "authors", "(local)", "pubs", "", "")

I have added the RunCode Action to my autoexec macro calling the function
name as: AttachDSNLessTable («stRemoteTableName», «stServer»,
«stDatabase»,
«stUsername», «stPassword»)

When I run it I get an error message telling me: "DBName, can't find the
name: "<<stRemoteTableName>>" you entered in the expression.

What are «stRemoteTableName», «stServer», «stDatabase», etc supposed to be?

The routine is expected string values to be passed to it. Assuming you're
trying to run this from within Access, «...» is not a syntax that means
anything.

 

[Trauton]

Douglas,

Thank you for your reply. We decided to use a Trusted Connection to solve
this issue. It worked very well.

Trauton

 

[Danny J. Lesandrini]

I've got an article about that at my site ...

http://amazecreations.com/datafast/GetFile.aspx?file=ODBCTutor02.htm&Article=true


--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast/

 

[Trauton]

Thanks Danny. I'll check it out.

Trauton

I've got an article about that at my site ...

http://amazecreations.com/datafast/GetFile.aspx?file=ODBCTutor02.htm&Article=true


--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast/