Connecting To SQL Server Local Instance (1 Viewer)

CNorway

Registered User.
Local time
Today, 05:01
Joined
Feb 13, 2018
Messages
33
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:01
Joined
May 7, 2009
Messages
19,175
Mabe you can upgrade to newer connector
 

CNorway

Registered User.
Local time
Today, 05:01
Joined
Feb 13, 2018
Messages
33
Hi,

Can you please send a link to what a newer connector is? Thanks

-Chris
 

CNorway

Registered User.
Local time
Today, 05:01
Joined
Feb 13, 2018
Messages
33
Hi,

OK... so, my connection method is just fine.

Perhaps I installed SQL server in an incorrect way that would make it so slow?

Thanks,
Chris
 

CNorway

Registered User.
Local time
Today, 05:01
Joined
Feb 13, 2018
Messages
33
I found that just by installing a SQL Server 2014 local instance and then importing the backup everything worked great. I never could figure out what the issue with the 2012 instance was. Though, as long as the speed is back to normal, I am happy. Thanks.
 

Users who are viewing this thread

Top Bottom