Excel VBA used to fetch Access data I am using the following VBA to fetch data from an Access database. The query works but there is a lot of information in the database and the response time is very slow, about 30 seconds.

Is there any part of the code that I could optimise or write differently?

Sub test()

Dim strsql1, tr As String
Dim rst As ADODB.Recordset

strsql1 = "SELECT * FROM FP WHERE SUBMISSION_ID = 5683;"


Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ace.OLEDB.12.0; Data Source=\\REDACTED.accdb;"

Set rst = New ADODB.Recordset

rst.Open strsql1, cn, adOpenStatic

End Sub
There's nothing to optimise here.

Your steps are:

Open connection  

Execute about the simplest SQL query possible  

End  


There's nothing we can change there that would make any difference.

Now, if you want to post a separate question, detailing your Database Structure, your SQL query, and asking how to optimise it, we could help there.



That aside, thoughts on your code:



You never use tr.



This:

Dim strsql1, tr As String


Does not actually Dim both as strings. When you declare variables, each variable must have a datatype or it will default to Variant.  

This is what your code is actually doing, implicitly:

Dim strsql1 [As Variant], tr As String




Your database connection string should be a constant

If you're targeting a database, and you don't need to build the connection string dynamically, you should just have a project constant like so:

Public const FP_DATABASE_CONNECTION_STRING As String = "Provider=Microsoft.ace.OLEDB.12.0; Data Source=\\REDACTED.accdb;"


Now it's in one place, and you know where it is, and if it ever changes, you know where to go to change it.



You should name your query

strsql1 tells me absolutely nothing useful. 

selectFpId5683 would be a much better name. Sure, it's a bit verbose, but it actually lets us know what the variable is. And now, when we use it later, we can know that we're using the right thing.

selectFpId5683 = "SELECT * FROM FP WHERE SUBMISSION_ID = 5683;"




And now:

Option Explicit

Public Const FP_DATABASE_CONNECTION_STRING As String = "Provider=Microsoft.ace.OLEDB.12.0; Data Source=\\REDACTED.accdb;"

Function GetAllFpRecordsWithId5683() As ADODB.Recordset

    Dim fpConnection As ADODB.Connection
    Set fpConnection = New ADODB.Connection
    fpConnection.Open FP_DATABASE_CONNECTION_STRING

    Dim selectFpId5683 As String
    selectFpId5683 = "SELECT * FROM FP WHERE SUBMISSION_ID = 5683;"

    Dim recordsWhereId5683 As ADODB.Recordset
    Set recordsWhereId5683 = New ADODB.Recordset
    recordsWhereId5683.Open selectFpId5683, fpConnection, adOpenStatic

    Set GetAllFpRecordsWithId5683 = recordsWhereId5683

End Function


Much, much easier to understand, work with, debug etc.



And then make it even better:

You'll probably want to target more than 1 ID in the future, so why not make your function a general one that can target any ID?

Option Explicit

Public Const FP_DATABASE_CONNECTION_STRING As String = "Provider=Microsoft.ace.OLEDB.12.0; Data Source=\\REDACTED.accdb;"

Function GetAllFpRecordsWithId(ByVal targetID As String) As ADODB.Recordset

    Dim fpConnection As ADODB.Connection
    Set fpConnection = New ADODB.Connection
    fpConnection.Open FP_DATABASE_CONNECTION_STRING

    Dim selectFpId As String
    selectFpId = "SELECT * FROM FP WHERE SUBMISSION_ID = " &amp; targetID &amp; ";"

    Dim recordsWhereId As ADODB.Recordset
    Set recordsWhereId = New ADODB.Recordset
    recordsWhereId.Open selectFpId, fpConnection, adOpenStatic

    Set GetAllFpRecordsWithId = recordsWhereId

End Function