Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search any part of an Access recordset from Excel
I have been desperately trying to figure this out and I'm stumped.
So I'm turning to my fellow Google Groupers. I have a macro enriched Excel file that calls/edits information in a MS Access database. There are about 20 fields in the database. I'm using ADODB connection/recordset codings to get/edit my information. I know how to code a SEARCH string in order to find one field of information, but not multiple fields. For example, I manage a database of permits. A few of the fields in my database are permit number (permit_num), County (county), Applicant (app), and Route Name (route_nam) - to name a few. If I wanted to search for the permit_num, my SEARCH would call that permit number. But what if the user didn't have a specific piece of information. I have 7 counties I deal with. What if the user knew the permit was in Richland county, but didn't know anything else. There could be 10 different permits for that county. Or, more specifically, what if the user only new it was Main St. There could be 50 different possibilities. If I go into the database myself, I can do an Edit, Find and type in a wild card name like "main", click Find and then scroll through the possibilities. I want to be able to do this through a VBA macro. I just don't know how to go about doing it in the most efficient way. Already, I can populate a Form Box with just a list of the permit numbers. The user types in the permit number and that is my search string variable that Excel goes into Access and finds. I want to have a Form Box where the user can type any variable and have Excel search for that in any field in Access and return a list of possible results. database name: Permit.Tracker table name: permit_info typical ADODB line: ' connect to the Access database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=Z:\COMMON FILES\Encroachment Permits \Permit.Tracker\Database\Permit.Tracker.mdb;" typical recordset search line looking for one piece of info: str = "SELECT permit_num FROM permit_info WHERE county = '" & sCounty & "'" rs.Open str, cn, adOpenKeyset, adLockOptimistic Any ideas? Anyone up for the challenge? Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search any part of an Access recordset from Excel
Your best bet would be a drop-down list of field names to choose from and a
text box for the user to enter a search term. You'd need to determine the type of the field selected, and then construct the query accordingly (quoted value if text, unquoted if numeric, etc). Tim "gab1972" wrote in message ... I have been desperately trying to figure this out and I'm stumped. So I'm turning to my fellow Google Groupers. I have a macro enriched Excel file that calls/edits information in a MS Access database. There are about 20 fields in the database. I'm using ADODB connection/recordset codings to get/edit my information. I know how to code a SEARCH string in order to find one field of information, but not multiple fields. For example, I manage a database of permits. A few of the fields in my database are permit number (permit_num), County (county), Applicant (app), and Route Name (route_nam) - to name a few. If I wanted to search for the permit_num, my SEARCH would call that permit number. But what if the user didn't have a specific piece of information. I have 7 counties I deal with. What if the user knew the permit was in Richland county, but didn't know anything else. There could be 10 different permits for that county. Or, more specifically, what if the user only new it was Main St. There could be 50 different possibilities. If I go into the database myself, I can do an Edit, Find and type in a wild card name like "main", click Find and then scroll through the possibilities. I want to be able to do this through a VBA macro. I just don't know how to go about doing it in the most efficient way. Already, I can populate a Form Box with just a list of the permit numbers. The user types in the permit number and that is my search string variable that Excel goes into Access and finds. I want to have a Form Box where the user can type any variable and have Excel search for that in any field in Access and return a list of possible results. database name: Permit.Tracker table name: permit_info typical ADODB line: ' connect to the Access database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=Z:\COMMON FILES\Encroachment Permits \Permit.Tracker\Database\Permit.Tracker.mdb;" typical recordset search line looking for one piece of info: str = "SELECT permit_num FROM permit_info WHERE county = '" & sCounty & "'" rs.Open str, cn, adOpenKeyset, adLockOptimistic Any ideas? Anyone up for the challenge? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Access recordset to import to Excel using ADO | Excel Programming | |||
Code for getting Access recordset into Excel | Excel Programming | |||
Access Recordset Rows to Excel | Excel Programming | |||
How to populate Excel Range from Access RecordSet? | Excel Programming | |||
Acquiring a single recordset from Access into Excel | Excel Programming |