Hi.
I have a Globally used Access DB. It has linked sharepoint tables, local tables, and linked SQL Server tables. The SQL Server tables are linked with ODBC. But, everyone in my company does not have the ODBC connection setup on their system. I don't want everyone to need to setup the ODBC on their system (people are lazy and won't do it). So, I created the connection in VB to the SQL server linked table and re-write it to a local table and then query off of that. But, it is slower than if they did have the ODBC. Code below:
Code:
Public Sub Update_Raw_Data_ADO()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim db As Object
Dim rs_local As Object
Dim variable_name As String
Dim sqltxt As String
'--- create connection to sql server table and open connection -----
cn.Provider = "sqloledb.1"
cn.Properties("Data Source").Value = "xxxxxxx.com"
cn.Properties("Initial Catalog").Value = "IDB"
cn.Properties("Integrated Security").Value = "SSPI"
cn.Open
'--- run sql against linked table -----
sqltxt = "SELECT (bla bla bla)"
Set rs = cn.Execute(sqltxt)
rs.MoveFirst
DoCmd.SetWarnings False
'--- delete local table contents -----
DoCmd.RunSQL "DELETE * FROM " & "[9c1) Raw Data Table Items Pass Thru - Local Qry]"
DoCmd.SetWarnings True
'--- Open local table and fill it with linked table -----
Set db = CurrentDb()
Set rs_local = db.OpenRecordset("9c1) Raw Data Table Items Pass Thru - Local Qry")
While Not rs.EOF
rs_local.AddNew
For i = 0 To rs.Fields.Count - 1
variable_name = rs(i).Name
rs_local(variable_name).Value = rs(variable_name).Value
Next i
rs_local.Update
rs.MoveNext
Wend
rs.Close
rs_local.Close
cn.Close
Set rs = Nothing
Set rs_local = Nothing
Set cn = Nothing
End Sub
Is there any way to put this vb connection info in the "Properties" of the linked table so they don't need the ODBC and not do it in VB and just do it in access queries???
Thanks.
Steve
Harrisburg, PA