Connecting to SQL Server 3

[sabloomer]

I am hoping that someone can help me with two problems that I am having.

One, I have created an File DSN to allow multiple users to connect to the SQL server. I choose "Use Trusted Connection" as the way for them to log in, and made sure that I added all of the users. The issue is that everytime a user tries to connect to the SQL server database a box pops up and they have to choose "Use Trusted Connection". Is there a way to make a File DSN that uses the trusted connection, but doesn't require the user to select that option?

Two,
I have a table that has invoice history for multiple years, it is a big table. When I try to run queries from Access off of that table I often ger "ODBC Timeout Expired" errors. How do I change the amount of time before the database times out?

Thanks for all of the help,

sabloomer

 

[cmmrfrds]

I like to setup the odbc connection is vba code and then run these functions. If you use a common login id then this only needs to be run once and you don't need to setup odbc on each PC.


Public Function SQLServerLinkedTable()
Dim oCat As ADOX.Catalog
Dim oTable As ADOX.Table
Dim sConnString As String

' Set SQL Server connection string used in linked table.
sConnString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server={Boxer};" & _
"Database=ReportInventory;" & _
"Trusted_Connection=No;" & _
"Uid=youruserid;" & _
"Pwd=yourpassword;"

' Create and open an ADOX connection to Access database
Set oCat = New ADOX.Catalog
oCat.ActiveConnection = CurrentProject.Connection

' Create a new Table object
Set oTable = New ADOX.Table

With oTable
.Name = "dbo_tblReportRequestPrelim"
Set .ParentCatalog = oCat
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Remote Table Name") = "dbo.tblReportRequestPrelim"
.Properties("Jet OLEDB:Cache Link Name/Password") = True
.Properties("Jet OLEDB:Link Provider String") = sConnString
End With

' Add Table object to database
oCat.Tables.Append oTable
oCat.Tables.Refresh
Set oCat = Nothing

End Function

Public Function SQLServerLinkedTableRefresh()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

Set cat = New ADOX.Catalog
Set tbl = New ADOX.Table

Dim sConnString As String

' Set SQL Server connection string used in linked table.
sConnString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server={Boxer};" & _
"Database=ReportInventory;" & _
"Trusted_Connection=No;" & _
"Uid=youruserid;" & _
"Pwd=yourpassword;"

' Open the catalog.
cat.ActiveConnection = CurrentProject.Connection
For Each tbl In cat.Tables
If tbl.Type = "LINK" And tbl.Name = "tblReportRequestPrelim" Then
tbl.Properties("Jet OLEDB:Link Datasource") = sConnString
End If
Next
End Function

 

[sabloomer]

Sorry for the delay in getting back to you. Thank you for the information. I have to admit that I am not the sharpest knife in the drawer when it comes to this topic.

The code you provided creates a linked table to the SQL server. Once this is run, it does not need to be run again. Will creating the table this way allow me to adjust the amount of time before it times-out?

Thank you,

sabloomer

 

[cmmrfrds]

You should be able to add the timeout property to the connection string. I don't know the property name, but it is probably something like. querytimeout or timeout or commandtimeout. Look up in odbc documentation.

 

[cmmrfrds]

This may be an alternate way to set the odbc timeout.

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

 

[sabloomer]

Thank you for your help.

sabloomer

 

[MartinGladis]

Hello,

I am very interested in this solution and like to try the code.

I get a compilation error for CurrentProject. What do I have to insert?

When do I have to call the function SQLServerLinkedTableRefresh ?

Best regards

Martin Gladis

 

[cmmrfrds]

The example uses ADOX so make sure you have a reference set.

'-- set reference to ADOX library
'- Microsoft ADO Ext. 2.6 for DDL and Security
'-- Microsoft ActiveX data objects 2.6 library also needed for ADO

The number could be anything from 2.1 to 2.8 depending on what version of the MDAC libraries you have on your PC. Best to use the lowest library number that works since if you copy the code to another PC that does not have as late a version as you have on your PC then it will have a missing reference. For example my PC has version 2.8 of the ADO library but I use version 2.5 so that it works on my users PC's since some have older versions.

I only do the relink on some users PC's that had older versions I needed to fix.