SQL Connection strinf - Spped IMPORTANT

[chrisbee]

Hi - I currently run an access database with what is really a flat table (55 fields) which grows by 10000 records a month until it reaches around 60000 when it auto archives off records that are more than 6 months old - very few relationships

We currently just use Access 'out of the box' and use a VPN and remote desktop between sites. (there are 2 remote sites)

My current connection string is:-

Dim CnConnect As ADODB.Connection
Dim MyRS As New ADODB.Recordset
Dim OkTable As New ADODB.Recordset
Set CnConnect = CurrentProject.Connection
Set MyRS = New ADODB.Recordset
Set OkTable = New ADODB.Recordset

searchcon = "[ConNote]= '" & DepotScan & "'"

MyRS.Open "Manifest", CnConnect, , adOpenStatic, adCmdTable
MyRS.Filter = searchcon

I have upsized to SQL and linked the tables but the code based on the above is DOG SLOW - I know I need to open an SQL link as opposed to the one above, and also to only pull over the records I need as opposed to a whole recordset and then filter......but I can't work it out - I have NO SQL experience.

Could someone point me in the right direction please?

Also each procedure opens and closes the connection currently - should I just open once at the start of the applicatiopn and close at the end?

Thanks