Connection to SQL server

[Newbee1000]

I have linked tables from a SQL server in a MS ACCESS.This SQL server is on a netwrok drive MS ACCess is on my local PC on the network. Every time I try to run a report or query I am asked to enter in my username and password. I want to automate that or enter it only once and not have to enter the authentication everytime.

I try to write a function as below:
"
Public Function Begin()
On Error GoTo Begin_Err
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim sConnString As String

cn.Open "DSN=PORTAL;" & _
"Uid=DBReview;" & _
"Pwd=take@h1ke"
Set cmd.ActiveConnection = cn

Begin_Exit:
Exit Function
Begin_Err:
MsgBox Error$
Resume Begin_Exit

End Function
"

and then want to run it from a Macro.But when I try to run it from a Macro it gives me an error that the function name entered is not found.

Please help.

 

[cmmrfrds]

What did you enter as the function name?

Is the function defined in a standard module?

 

[Newbee1000]

The Function name is Begin()and it is defined in the code.

 

[cmmrfrds]

What you show in your example is code. The question is where did you define it. Is the function defined in a standard module?

 

[jrbarnett]

There are two issues here:

1. If you operate through Linked tables, your database connections run through Jet rather than your ADO databas connection object. To rewrite the lot to operate exclusively from SQL server and ADO is possible but a lot of work.
If it is done properly, you won't need linked tables.

2. Your ADO connection object is defined in the Begin() function and called from within it, but not closed when you finish (add cn.Close at the end to fix this).
This will cause database connections to be left open, which you can't close from elsewhere, so you will have to wait for it to time out. This will use SQL client access licenses up if configured in Per Seat mode.

I'd recommed moving your database connection functions to a dedicated module and have functions in there called throughout to run commands against the database, so that only one connection is needed.

John

 

[Newbee1000]

All I am trying to do is to have the MS Access databse login directly to the SQL databse.

Is there any quick fix?

 

[cmmrfrds]

When you link the tables there is an option to save password, check that option.