"Should I use Singleton pattern to persist connection lifetime?"
I Singleton pattern suggest that you have a Global variable used by multiple procedures. Using this pattern you will either have to have a single procedure open and close the connection or test that the connection is open at every point of use. The problem with having to test that is open is that you will also have to decide if another procedure is using the connection or just leave it open anyway. It would be far easier to create and open the connection in your
Main() procedure and pass it as a parameter to any secondary methods.
"I'm sure it's better to retrieve all customers at once but what's next? How do I retrieve customers that easily?"
This is a pretty broad statement and requires more information for a definitive answer.
How many fields and or tables do you want to retrieve? In your question you listed 1 table and 2 fields. In your comments to @AJD you introduced
GetProduct. I am assuming that these are
Products is probably a separate table and will probably only require that updating a few records at once. Where as,
Address probably belongs to the
Customer table and
Customer implies that you may be syncing hundreds, thousands, or more records at once. These are very different problems that require different approaches.
If you only need to retrieve a couple of hundred records, opening and closing the connection after each call will not have much impact to your performance. When working with large datasets, querying all the necessary records at once will greatly improve your performance.
Using Microsoft Access SQL
IN() Operator and Clause to limit Selected Record
Reference: MSDN:In Operator (Microsoft Access SQL)
The Microsoft Access SQL
IN() Operator will limit the dataset to include only records that have values in a field contained in a value list:
SELECT Customer FROM Customers WHERE Customers.ID IN(1,2,3)
Reference: MSDN:IN Clause (Microsoft Access SQL)
The Microsoft Access SQL
IN() Clause retrieves records from an external datasource:
SELECT CustomerID, CompanyName
IN "c:\documents\xldata.xls" "EXCEL 5.0;"
Combining the two techniques, we can limit the records selected from the OP's database.Customers table to include only records that are in the
ID field of
Worksheets("Sheet1") like this:
SELECT ID, Customer FROM [Customers] WHERE [Customers].ID
SELECT ID FROM [Sheet1$] IN '\example.xlsm'[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;']
Updating Excel.Customers From Access.Customers
I do not know if it is possible to directly update Excel from an external data source using an ADO Query. What you can do is retrieve the records, copy the recordset to a temp worksheet and then run a second query to update the records on the main worksheet.
Here is a simplified working example of how to update an Excel table from Access. I wrote this code, in such a way, that it would be easy to follow. From here, I would recommend extracting several methods including
getTempWorksheet and possibly
getXLConnString(FileName,WorkSheetName) and add Error Handlers.
Public Sub UpdateExcelCutomers()
Dim ConnectionString As String, SQL As String
Dim FieldNames() As Variant
Dim conn As Object, rs As Object
Dim j As Long
'Access Database Connection String
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\db.accdb" & ";Persist Security Info=False;"
Set conn = CreateObject("ADODB.Connection")
FieldNames = Array("ID", "Customer", "First Name", "Last Name")
'Retrieve Customers Fields From Access Database WHERE ID IN Excel Customers.ID
SQL = _
"SELECT [" & Join(FieldNames, "],[") & "] FROM [Customers] WHERE [Customers].ID IN(SELECT ID FROM [Customers$]" & vbNewLine & _
"IN '" & ThisWorkbook.FullName & "'[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] )"
Set rs = conn.Execute(SQL)
.Range("A1").Resize(1, UBound(FieldNames) + 1).Value = FieldNames
'Close the Connection to the Access Database
'Excel Connection String
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 8.0;HDR=YES"";"
'Update Excel Cumstomer
SQL = "UPDATE [" & .Name & "$] INNER JOIN [Customers$] ON [" & .Name & "$].ID = [Customers$].ID SET "
For j = 1 To UBound(FieldNames)
If j > 1 Then SQL = SQL & ","
SQL = SQL & "[Customers$].[" & FieldNames(j) & "] = [" & .Name & "$].[" & FieldNames(j) & "]"
Application.DisplayAlerts = False
Application.DisplayAlerts = True