Function to Link to a Database -- SQL from Access (1 Viewer)

Hello,

I am in need of a function that will automatically link to my SQL DB upon logging into the App. I have set up an AUTOEXEC command that calls a module. In the module I have the following code:

Dim cnlink As New ADODB.Connection
Dim cnlink As New ADODB.Connection
Dim rst As ADODB.Recordset
Dim strConnectionString As String
Dim strSQL As String

Set cnlink = New ADODB.Connection
strConnectionString = "Provider=SQLOLEDB;" & _
"Data Source=SERVERNAME;" & _
"Initial Catalog=DBNAME;" & _
"User ID=sa" & _
";Password=password"
cnlink.Open (strConnectionString)

However, when I try to add it to Macro for Action RUN CODE I put in the name of the module which is RelinkDB(). I am getting an error like the following when trying to run this: expression you entered has a function name that Access can't find.
Any ideas on why I am getting this error? If I could get this to work then all tables to this Database would link on open rather than prompting for DB user name and password each time connect (for 1st time) to that DB. Isn't that correct? Any help would be greatly appreciated.

The error is because you cannot "call" a module as such, you need to be calling a Function or Subroutine within a module.
Wrap Public Sub ReLinkDB ..... End Sub around the code, you won't get the error.
Having said that, this isn't the way to resolve your password problem, as the connection object created is not then used for anything.
The solution depends on whether you are
using an Access database with linked tables (via ODBC), or an Access data-project (OLE DB).
It could be as simple as deleting the links, and then re-linking, this time ticking the box to remember the password.

Link Function

Thank you Mearle. I just relinked and it worked but I thought if the database restarts (reboot), etc... you have to sign in again. Is this not the case as are as you know. Is there a better way using VBA to establish this link?
Also, I have changed the module to be as follows:

Public Sub RelinkDB()
Dim cnlink As New ADODB.Connection
Dim cnlink As New ADODB.Connection
Dim rst As ADODB.Recordset
Dim strConnectionString As String
Dim strSQL As String

Set cnlink = New ADODB.Connection
strConnectionString = "Provider=SQLOLEDB;" & _
"Data Source=SERVERNAME;" & _
"Initial Catalog=DATABASE;" & _
"User ID=USER" & _
";Password=PASSWORD"
cnlink.Open (strConnectionString)
End Sub

However, I am still getting the error when trying to call the module. Any ideas?
Thanks again.

Access is capable of storing authentication details for a linked table, so it depends on whether you provided the information when you did the link, and got access to remember it.
As for the Macro error, either access is being fussy by requiring the "function" to be fully qualified with the module name - so use RelinkDB.RelinkDB if your module is called the same....
or it really does require a function, rather than a sub, so replace sub with function (and end function).
or possibly both - but anyway, your routine doesn't do anything useful.
Just out of interest, key the following in the "immedidate" window of your module: (replace xxxxx with the name of one of your linked tables)
?DBEngine.Workspaces(0).Databases(0).TableDefs("xxxxxx").Connect

This should display the ODBC connection string used, and you will be able to see whether username/password are included.

Relink Function

Mearle,
I tried to run the code below in the Immediate window to one of the tables that I am working with:
?DBEngine.Workspaces(0).Databases(0).TableDefs("F4101").Connect

F4101 is the table name, however; it says Item Not Found in this Collection.
Any add'l ideas on this are much appreciated.
Thanks,

Call Relink DB function

Hi Mearle,
I put relinkdb.relinkdb in the Autoexec but it still says it Cannot find the function. If it doesn't make any difference then I won't worry about trying to get this to work.

Thanks again.

Re the macro error, just tried it myself, and you do need a function, not a sub, and it needs to be followed by (). The "..." button will find it.
The table name needs to be as it is in Access, not SQL Server - and these usually default to being named starting with "dbo_" - if you are using a normal Access database, rather than a project - though you may have re-named them.

Otherwise have a poke around in the immediate window - try using numbers instead of names to see which tables you can reference - eg the following:
?DBEngine.Workspaces(0).Databases(0).TableDefs.count
(you'll be able to access tables 0 to the count-1)
?DBEngine.Workspaces(0).Databases(0).TableDefs(2).name
?DBEngine.Workspaces(0).Databases(0).TableDefs(2).connect
?DBEngine.Workspaces(0).Databases(0).name

Change to function

Hi Mearle,

I changed the module to the following:

Public Function RelinkDB()
Dim cnlink As New ADODB.Connection
Dim cnlink As New ADODB.Connection
Dim rst As ADODB.Recordset
Dim strConnectionString As String
Dim strSQL As String

Set cnlink = New ADODB.Connection
strConnectionString = "Provider=SQLOLEDB;" & _
"Data Source=SERVERNAME;" & _
"Initial Catalog=DATABASENAME;" & _
"User ID=sa" & _
";Password=PASSWORD"
cnlink.Open (strConnectionString)
End Function

I also changed the AutoExec to call RelinkDB(). I clicked on the ... and double clicked my module and added () to the end but it still says it cannot find the expression.
I know you got it to work so do you have any ideas on what I am doing incorrectly?

Also, I ran the script you gave me and below is the output from Immediate Window:

?DBEngine.Workspaces(0).Databases(0).TableDefs.count
20
?DBEngine.Workspaces(0).Databases(0).TableDefs(2). name
CRPDTA_F4100
?DBEngine.Workspaces(0).Databases(0).TableDefs(2). connect
ODBC;DSN=Business Data - CRP;Description=Business Data - CRP;APP=2007 Microsoft Office system;DATABASE=PS_CRP;Network=DBMSSOCN;AutoTranslate=No;UseProcForPrepare=0
?DBEngine.Workspaces(0).Databases(0).name
\\Filelocation\filename.accdb

I am not sure exactly what this is telling you, though. I really appreciate your help.

Thank you.

If the Macro Action is "RunCode", and the "Function Name" is ReLinkDB (), and Public Function ReLinkDB exists (module has been saved), then it should work.
The data from the Immediate windows tells me that table 2 is a linked table called CRPDTA_F4100, so likely the the table that it couldn't find before is really called CRPDATA_F4101 in Access, rather than F4101.
The Connect string, tells me that you used a DSN (Data source name) called "Business Data - CRP" to link to the database, and that no username or password is included in this connect string.
DSN's can be viewed in Control Panel -Administrative Tools - Data Sources (ODBC), or by keying in odbcad32.exe into the Start-Run box.
I guess you need to find the DSN "Business Data - CRP" in one of the 1st 3 tabs, and configure it so the username and password are included in this DSN, then all linked tables using it will benefit.
If this works, then the only issue with it is security, in that anybody with access to this DSN will be able to access the SQL database.