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

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

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

End Sub

• There really isn't anything to optimize in the posted code in terms of performance. If opening the connection and fetching the result takes 30 seconds, then it takes 30 seconds. If you told us about the FP table, what indexes it has, what you intend to do with the result, then maybe we could help. – 200_success Jul 28 '16 at 9:36
• The only other suggestion I would have is to optimise the database, and only return the necessary fields, rather than *. – SeanR Jul 28 '16 at 14:57

There's nothing to optimise here.

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.

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;"

fpConnection.Open FP_DATABASE_CONNECTION_STRING

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

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

fpConnection.Open FP_DATABASE_CONNECTION_STRING

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