Hi,
I have been using a local SQL Server 2012 Instance which I link to using the below VBA code. Everything has been going fantastic for years until I upgraded my computer recently. I installed a local instance of SQL Server 2012 on my computer and then imported the database that stores the back-end tables which access uses.
The problem is that suddenly every table is taking about 5X more time to load. Even though, my computer is 2X faster than my old computer.
Any help would be great appreciated!
Also, if there is a better way these days to connect to tables in SQL Server, I am interested in changing the code I am using. I have been using the below code for 4-5 years and it has worked great until just now. Thanks again!
Best Regards,
Chris
Function AddDSNLessTable(stLocalTableName As String, stRemoteTableName As String, strSQLServerHost As String, strSQLDatabase As String, Optional strSQLLogin As String, Optional strSQLPswd As String)
On Error GoTo Error
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
On Error Resume Next
CurrentDb.TableDefs.Delete (stLocalTableName)
On Error GoTo Error
If Len(strSQLLogin) = 0 Then
'//Use trusted authentication
stConnect = "ODBC;Driver={SQL Server};SERVER=" & strSQLServerHost & ";DATABASE=" & strSQLDatabase & ";Trusted_Connection=Yes;"
Else
stConnect = "ODBC;Driver={SQL Server};SERVER=" & strSQLServerHost & ";DATABASE=" & strSQLDatabase & ";UID=" & strSQLLogin & ";PWD=" & strSQLPswd
End If
Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append td
AddDSNLessTable = True
Exit Function
Error:
AttachDSNLessTable = False
MsgBox "Error connecting table " & stLocalTableName & ": " & Err.Description
End Function
Public Sub CreateIndexonView(strIndexName As String, strViewName As String, strFields As String)
Dim strSQL As String
strSQL = "Create Index " & strIndexName & " On " & strViewName & "(" & strFields & ")"
CurrentDb.Execute strSQL
End Sub
I have been using a local SQL Server 2012 Instance which I link to using the below VBA code. Everything has been going fantastic for years until I upgraded my computer recently. I installed a local instance of SQL Server 2012 on my computer and then imported the database that stores the back-end tables which access uses.
The problem is that suddenly every table is taking about 5X more time to load. Even though, my computer is 2X faster than my old computer.
Any help would be great appreciated!
Also, if there is a better way these days to connect to tables in SQL Server, I am interested in changing the code I am using. I have been using the below code for 4-5 years and it has worked great until just now. Thanks again!
Best Regards,
Chris
Function AddDSNLessTable(stLocalTableName As String, stRemoteTableName As String, strSQLServerHost As String, strSQLDatabase As String, Optional strSQLLogin As String, Optional strSQLPswd As String)
On Error GoTo Error
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
On Error Resume Next
CurrentDb.TableDefs.Delete (stLocalTableName)
On Error GoTo Error
If Len(strSQLLogin) = 0 Then
'//Use trusted authentication
stConnect = "ODBC;Driver={SQL Server};SERVER=" & strSQLServerHost & ";DATABASE=" & strSQLDatabase & ";Trusted_Connection=Yes;"
Else
stConnect = "ODBC;Driver={SQL Server};SERVER=" & strSQLServerHost & ";DATABASE=" & strSQLDatabase & ";UID=" & strSQLLogin & ";PWD=" & strSQLPswd
End If
Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append td
AddDSNLessTable = True
Exit Function
Error:
AttachDSNLessTable = False
MsgBox "Error connecting table " & stLocalTableName & ": " & Err.Description
End Function
Public Sub CreateIndexonView(strIndexName As String, strViewName As String, strFields As String)
Dim strSQL As String
strSQL = "Create Index " & strIndexName & " On " & strViewName & "(" & strFields & ")"
CurrentDb.Execute strSQL
End Sub