Pulling data from Access Database to Excel

[DanSMT]

I'm trying to pull data from access database to excel base on the value of an array of textboxes in a user form. The data would be placed into a blank sheet in the workbook. Any assistance would be great, I think I'm on the right track, but cant find the mistake that is causing the error.

'Declaring variables.
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim dbPath As String
Dim SQL As String
Dim var
'add error handling
On Error GoTo errHandler:
'Disable screen update.
Application.ScreenUpdating = False
'clear worksheet
Worksheets("Data").Range("A2:ZZ10000").ClearContents
'get the path to the database
dbPath = "C:\Users\dans\Documents\Dest2\Database.accdb"

Set cnn = New ADODB.Connection

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath

For a = 1 To 24
var = Me("Part" & a)
SQL = "SELECT [Part Number] FROM RIDatabase WHERE [Part Number] = '" & var & "'"

'Create the ADODB recordset object.
Set rs = New ADODB.Recordset 'assign memory to the recordset

rs.Open SQL, cnn

'Check if the recordset is empty.
If rs.EOF And rs.BOF Then
'Close the recordset and the connection.
rs.Close
cnn.Close
'clear memory
Set rs = Nothing
Set cnn = Nothing
'Enable the screen.
Application.ScreenUpdating = True
'In case of an empty recordset display an error.
MsgBox "There are no records in the recordset!", vbCritical, "No Records"

Exit Sub
End If

'Write the recordset values in the sheet.
Sheet2.Range("A1").CopyFromRecordset rs

'Close the recordset and the connection.
rs.Close
cnn.Close
'clear memory
Set rs = Nothing
Set cnn = Nothing
'loop
Next a
'Enable the screen.
Application.ScreenUpdating = True

'Inform the user that the macro was executed successfully.
MsgBox "Congratulation the data has been successfully Imported", vbInformation, "Import successful"
'error handler
On Error GoTo 0
Exit Sub
errHandler:
'clear memory
Set rs = Nothing
Set cnn = Nothing

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Import_Data"

Thanks in advance!

 

[DanSMT]

I've resolved this. Updated code for others just incase someone needs it some day.

'Declaring variables.
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim dbPath As String
Dim SQL As String
Dim var
'add error handling
On Error GoTo errHandler:
'Disable screen update.
Application.ScreenUpdating = False
'clear worksheet
Worksheets("Data").Range("A2:ZZ10000").ClearContents
'get the path to the database
dbPath = "C:\Users\dans\Documents\Dest2\Database.accdb"

Set cnn = New ADODB.Connection

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & ";Jet OLEDB:RIDatabase"

For a = 1 To 24
var = Me("Part" & a)
If var = "" Then
Exit Sub
End If

SQL = "SELECT * FROM RIDatabase WHERE [Part Number] = '" & var & "'"

'Create the ADODB recordset object.
Set rs = New ADODB.Recordset 'assign memory to the recordset

rs.Open SQL, cnn

'Check if the recordset is empty.
If rs.EOF And rs.BOF Then
'Close the recordset and the connection.
rs.Close
cnn.Close
'clear memory
Set rs = Nothing
Set cnn = Nothing
'Enable the screen.
Application.ScreenUpdating = True
'In case of an empty recordset display an error.
MsgBox "There are no records in the recordset!", vbCritical, "No Records"

Exit Sub
End If

'Write the recordset values in the sheet.
Worksheets("Data").Range("A1").CopyFromRecordset rs

'Close the recordset and the connection.
rs.Close
cnn.Close
'clear memory
Set rs = Nothing
Set cnn = Nothing
'loop
Next a
'Enable the screen.
Application.ScreenUpdating = True

'Inform the user that the macro was executed successfully.
MsgBox "Congratulation the data has been successfully Imported", vbInformation, "Import successful"
'error handler
On Error GoTo 0
Exit Sub
errHandler:
'clear memory
Set rs = Nothing
Set cnn = Nothing

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Import_Data"

Areas is missed were the Database callout section shown below;

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & ";Jet OLEDB:RIDatabase"

and, and if statement closing out the sub when textboxes are blank.

If var = "" Then
Exit Sub

 

[smozgur]

Updated code for others just incase someone needs it some day.

Thanks for posting your solution and sharing it with the others!