1

I would like to imort data from a table(Table1) in Access programaticly to Excel. I have two search boxes on a userform (txtCr1 & txtCr2) and a button to execute the code. Each search box corresponds to a field in Table1. I would like to search use "AND" to search both fields. I don't know what I'm missing in my code but I get error 3001 when I run it. Please suggest on how to fix this.

Thank you for your help in advance.

Private Sub CommandButton5_Click()

'Declaring the necessary variables.
Dim cnn As ADODB.Connection 'dim the ADO collection class
Dim rs As ADODB.Recordset 'dim the ADO recordset class
Dim dbPath As String
Dim SQLwhere As String
Dim SrtSql As String
Dim i As Integer
Dim var
Dim var2
'add error handling
On Error GoTo errHandler:
'Disable screen flickering.
Application.ScreenUpdating = False
'clear the values from the worksheet
Sheet5.Range("A2:AD499").ClearContents

'get the path to the database
dbPath = Sheet5.Range("H500").Value

'set the search variable
var = UserForm1.txtCrt1
var = UserForm1.txtCrt2

Set cnn = New ADODB.Connection ' Initialise the collection class variable

'Connection class is equipped with a —method— named Open
'—-4 aguments—- ConnectionString, UserID, Password, Options
'ConnectionString formula—-Key1=Value1;Key2=Value2;Key_n=Value_n;
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath


'Create the SQL statement to retrieve the data from table.


SQLwhere = "WHERE "

If Len(var & vbNullString) <> 0 Then

SQLwhere = SQLwhere & "[Table1].[RunDate] = '" & var & "' AND "

End If

If Len(var2 & vbNullString) <> 0 Then
SQLwhere = SQLwhere & "[Table1].[SampleID] = '" & var2 & "' AND "
End If

StrSql = "SELECT * FROM [Table1] "

 'Remove the last AND applicable

If SQLwhere = "WHERE " Then
    SQLwhere = ""
Else
    SQLwhere = Left(SQLwhere, Len(SQLwhere) - 4)
End If

StrSql = StrSql & SQLwhere


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

'ConnectionString Open '—-5 aguments—-
'Source, ActiveConnection, CursorType, LockType, Options
rs.Open sql, cnn

'Check if the recordset is empty.
If rs.EOF And rs.BOF Then
'Close the recordet 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"
'Me.lstDataAccess.RowSource = ""
Exit Sub
End If

'Write the reocrdset values in the sheet.
Sheet5.Range("A2").CopyFromRecordset rs

'Close the recordset and the connection.
rs.Close
cnn.Close
'clear memory
Set rs = Nothing
Set cnn = Nothing

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

End Sub
Community
  • 1
  • 1
user3781528
  • 623
  • 6
  • 27
  • 1
    I'm guessing that error occurs at this line: `rs.Open sql, cnn` At that point, `sql` is an empty undeclared variable. Do you get what you expect, or at least a different error, if you use this line instead? ... `rs.Open StrSql, cnn` – HansUp Jun 20 '15 at 16:30
  • 1
    Add `Option Explicit` to your module's Declarations section. Then run Debug->Compile from the VB Editor's main menu. Fix anything the compiler complains about. Then try compile again. Repeat until no more compile errors. If you still need help, show us your code which compiles without error with `Option Explicit` – HansUp Jun 20 '15 at 16:33
  • 1
    I'm assuming `var = UserForm1.txtCrt2` should be `var2 = UserForm1.txtCrt2`? – Comintern Jun 20 '15 at 16:34

0 Answers0