Quering Access via Excel

M

MilaK

Registered User.
Local time
Yesterday, 23:30
Joined
Feb 9, 2015
Messages
285
Hello,

I have one table in Access with 6 different fields. I would like the user to query each of these fields from a userform in Excel.
For example, I have 6 fields:LastName, Firstname, Address, Age, Gender, Email. I've created 6 different text boxes for each of these fields on a userform in Excel. I also have a Checkbox that determines if the search criteria is exact or "like".

I know how to search a single field (see code below) but I'm not sure how to make this work with multiple fields? How do I know which field the user is using for searchng? Or how many SQL statements do I need? Is it possible to search by both FirstName and Age.

Please suggest how to accomlish this,

Thank you very much,

Mila:)

Code example:

Code: 
Dim var

'set the search variable
var = Me.txtSearch

If CheckBox1 = True Then

SQL = "SELECT * FROM PhoneList WHERE LastName = '" & var & "'"
Else
SQL = "SELECT * FROM PhoneList WHERE LastName LIKE '" & var & "%" & "'"
End If
Here is the entire routine:

Code: 
Private Sub cmdImport_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 SQL As String
Dim i As Integer
Dim var
'add error handling
On Error GoTo errHandler:
'Disable screen flickering.
Application.ScreenUpdating = False

'get the path to the database
dbPath = Sheet1.Range("I3").Value

'set the search variable
var = Me.txtSearch

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.

If CheckBox1 = True Then

SQL = "SELECT * FROM PhoneList WHERE SURNAME = '" & var & "'"
Else
SQL = "SELECT * FROM PhoneList WHERE SURNAME LIKE '" & var & "%" & "'"
End If

'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.
Sheet2.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

Me.lstDataAccess.RowSource = "DataAccess"
'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
 
CopyFromRecordset is in itself resource intensive and it's not an action you would want to perform each time a search is run. Of course this is dependent on the volume of your data. So how big is your dataset?
 
It's not big right now but I expect it to grow. What do you suggest?

Thanks,

Mila
 
I would suggest a couple of things:

* Move it to Access and create a search form there.
* Look into search/filter methods within Excel
* Use disconnected recordsets and search locally (i.e. the disconnect recordset) before pushing to the sheet using CopyFromRecordset
* Research active x controls that bind to data and allow filtering
* Create your own search function that can search within the array of a range

Options 1 and 2 being my preference.
 
I will research your suggestions but it will take me many months to figure this out since I'm novice at this. Access is new to me.
Please suggest how I can add on to what I already have to make this work for now.
1. search by any field.
2. search by a combination of two fields

code examples would be greatly appreciated.

Thank you,

Mila:)
 
Hello,

Do you think this would work? Am I missing some apostrophes?
I have two textboxes in the following example:

Code: 
var = Me.txtCrt
var2 = Me.txtCrt2
Dim SQLwhere As String
Dim StrSql As String


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

SQLwhere = “WHERE “
If Len(var & vbNullString) <> 0 Then

SQLwhere = SQLwhere & "[MDL_IonTorrent].[RuInfo] = " & var & " AND "
  
End if

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

StrSql = "SELECT * FROM [MDL_IonTorrent] "

 'Remove the last AND applicable

If SQLwhere = "Where " Then
    SQLwhere = ""
Else
    SQLwhere = Left(SQLwhere, Len(SQLwhere) - 4)
End If
 
StrSql = StrSql & SQLwhere

Thank you,

Mila:)
 
Tidied your code a little... see the comments
Code: 
    Dim strFilter As String
    
    Const STR_AND As String = " AND "
    
    'Create the SQL statement to retrieve the data from table.
    If Len(Me.txtCrt & vbNullString) <> 0 Then
        strFilter = "[RuInfo] = '" & Me.txtCrt & "'" & STR_AND
    End If
    
    If Len(Me.txtCrt2 & vbNullString) <> 0 Then
        strFilter = strFilter & "[SampleID] = " & Me.txtCrt2 & STR_AND
    End If
    
    ' Remove the last AND applicable
    If strWhere <> vbNullString Then
        strFilter = Left(strFilter, Len(strFilter) - Len(STR_AND))
        
        ' Apply the filter string to an already open recordset
        rs.Filter = strFilter
        ' // Clear the sheet here
        Sheet2.Range("A2").CopyFromRecordset rs
    End If
 
Hello,

I don't need to construct the "Where" clause? I got this to work with my example but I will definitely give your code a try.

1. I'm exploring querying multiple Access tables from Excel instead of just one. Could you please point me to some sample code that would give me a head start?

2. One more question...I have a routine that sends the data to the same table in Access and it works well if I fill out the "ID" column in Excel and set "ID" field in Access to "number". However, I would like the ID field to populate automatically. I set the ID field to "Auto number" in Access and left that column blank in Excel and was hoping that this will do the trick. However, the data doesn't transfer to Access. Could you please suggest how to make this work?

Thank you very much for all of your help!

This is so much fun...

Mila :)
 
MilaK said:
I don't need to construct the "Where" clause? I got this to work with my example but I will definitely give your code a try.
Click to expand...
It's not a matter of whether you need or don't need it, I'm giving you code that is more efficient. Building a WHERE clause and opening a new recordset each time is slower than using the Filter property on an already opened recordset.

MilaK said:
1. I'm exploring querying multiple Access tables from Excel instead of just one. Could you please point me to some sample code that would give me a head start?
Click to expand...
You simply open multiple recordsets using the same connection.

MilaK said:
2. One more question...I have a routine that sends the data to the same table in Access and it works well if I fill out the "ID" column in Excel and set "ID" field in Access to "number". However, I would like the ID field to populate automatically. I set the ID field to "Auto number" in Access and left that column blank in Excel and was hoping that this will do the trick. However, the data doesn't transfer to Access. Could you please suggest how to make this work?
Click to expand...
The INSERT statement that you run should include the relevant fields minus the Autonumber field and it will auto increment.
 
The INSERT statement that you run should include the relevant fields minus the Autonumber field and it will auto increment.
Click to expand...

Are you suggesting to delete column "ID" on the worksheet and set the field "ID" in Access to Auto-number? I've tried this but I get an error message.

Thanks,

Mila
 
Please let me know if understood you correctly. Is this what you've suggested to do?

from this:

Code: 
'add value to record set object
For x = 2 To nextrow
rst.AddNew
For i = 1 To 4
rst(Cells(1, i).Value) = Cells(x, i).Value
Next i
rst.Update
Next x

to this?

Code: 
add value to record set object
For x = 2 To nextrow
rst.AddNew
For i = 2 To 4
rst(Cells(1, i ).Value) = Cells(x, i).Value
Next i
rst.Update
Next x

I've made a brand new table and set ID field to Auto-number and made the change in the code described above. Unfortunately, I get a false message that data transfere worked, however, when I open Access and refresh the table is empty. I'm not sure why this is happening.

Thanks,

Mila:banghead:
 
Last edited:
Yes, you can't insert a value into an autonumber field so it needs to be skipped.
 
Yes, you can't insert a value into an autonumber field so it needs to be skipped.
Click to expand...

I have skipped it, I set i to 2.
 
What do you mean by "refresh the table"? Did you close and open the table?
 
What do you mean by "refresh the table"? Did you close and open the table?
Click to expand...

Yes, I have.. the data doesn't show up.

:confused:

Mila
 
Last edited:
Do you have any error handling in your code? What is the cursor location?

Also try firing an INSERT statement as previously mentioned. Use the Command object with the Connection object.
 
I do have error handling.

Code: 
On Error GoTo errHandler:

Cursor?

Code: 
CursorType:=adOpenDynamic

Please suggest how to fire INSERT statement?

Thank you,

Mila


Code: 
Private Sub CommandButton4_Click()

Dim cnn As ADODB.Connection 'dim the ADO collection class
Dim rst As ADODB.Recordset 'dim the ADO recordset class
Dim dbPath
Dim x As Long, i As Long
Dim nextrow As Long

'add error handling
On Error GoTo errHandler:

'Variables for file path and last row of data
dbPath = ActiveSheet.Range("H500").Value

'FName
nextrow = Cells(Rows.Count, 1).End(xlUp).Row

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

'Check for data
'Worksheets("Access").Activate

If Sheet5.Range("B2").Value = "" Then
MsgBox " Add the data that you want tot send to MS Access"
Exit Sub
End If


cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
'two primary providers used in ADO SQLOLEDB —-Microsoft.JET.OLEDB.4.0 —-Microsoft.ACE.OLEDB.12.0
'OLE stands for Object Linking and Embedding, Database

'ADO library is equipped with a class named Recordset
Set rst = New ADODB.Recordset 'assign memory to the recordset

rst.Open Source:="MDL_IonTorrent", ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
Options:=adCmdTable

'add the values to it
For x = 2 To nextrow
rst.AddNew
For i = 2 To 5
rst(Cells(1, i).Value) = Cells(x, i).Value
Next i
rst.Update
Next x

'close the recordset
rst.Close
' Close the connection
cnn.Close
'clear memory
Set rst = Nothing
Set cnn = Nothing

'communicate with the user
MsgBox " The data has been successfully sent to the access database"

'Update the sheet
Application.ScreenUpdating = True

'show the next ID
'Sheet1.Range("J3").Value = Sheet1.Range("K3").Value + 1

'Clear the data
Sheet5.Range("A2:AD499").ClearContents
On Error GoTo 0
Exit Sub
errHandler:

'clear memory
Set rst = Nothing
Set cnn = Nothing
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Export_Data"

End Sub
 
When you're testing code, comment out the On Error GoTo line.
 
vbaInet said:
When you're testing code, comment out the On Error GoTo line.
Click to expand...

I've commented out all error handlers and get the same message "data has been successfully sent to Access" but the table is empty.

Thanks,

Mila:confused:
 
You must log in or register to reply here.

Similar threads

N
Solved MS Access Outlook VBA fixing
Replies
5
Views
754
nector
N
N
How to ensure that the current stock control is update when the data appears on the subform in access
Replies
5
Views
825
Minty
Minty
N
Solved How get lid of domain function and replace them with opening recordset in MS Access VBA
Replies
10
Views
472
nector
N
D
Calendar displaying autonumber from a combo box..
Replies
4
Views
714
theDBguy
theDBguy
A
Secure Storage Model for Microsoft Access: Stream-Based Encrypted Data Architecture(for discussion)
Replies
5
Views
375
GPGeorge
GPGeorge

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom