Search a database with multiple strings?

[gab1972]

I have an MS Access database that I am pulling and sending information
from/to. I have one table (permit_info) and about 20 different
fields.

I have a userform in Excel that the user can type in a permit number
and it will find the recordset matching that number (my table has a
field with permit numbers in it). What I want to do is allow the user
to search by multiple fields. Say, the person doesn't know the permit
number and wants to search by county and/or applicant name. Can I do
a keyword search box on my user form and have Excel return possible
hits?

I'm guessing some kind of loop that will search by say 3 different
fields, go into Access and pull the recordsets matching those 3
fields, store them temporarily in Excel on a hidden sheet, and then
display them in a combobox???

I'm using Office2k3.
Database path/name: P:\Permit Tracking System Project\Tracking System
Project\Permit.Tracker_v0.01109b.mdb
Table name: permit_info

I'm using ADO to communicate with Access.

Can anyone get me started? Thanks in advance.

 

[Joel]

Excel uses the same SQL statements that would be used in Access. If you set
up the search you desire in Access and provide the SQL statments these can be
put into an Excel macro.. You can rretrieve the data used one compond SQL
statements that uses multiple criteria. One you get the recordset you can
pull the data directly from the returned recordset to the combobox. The
Recordset is a pointer to the data and you can extract that data as required.
It doesn't need a temporary worksheet for the results.

If you used a query, then you would have to put the results termporarily
into a worksheet before displaying the results in the comboxbox..

 

[gab1972]

Excel uses the same SQL statements that would be used in Access.  If you set
up the search you desire in Access and provide the SQL statments these can be
put into an Excel macro..  You can rretrieve the data used one compond SQL
statements that uses multiple criteria.  One you get the recordset you can
pull the data directly from the returned recordset to the combobox.  The
Recordset is a pointer to the data and you can extract that data as required.
 It doesn't need a temporary worksheet for the results.

If you used a query, then you would have to put the results termporarily
into a worksheet before displaying the results in the comboxbox..

I'm not real familiar with SQL...can you do this using ADO?

 

[Joel]

Below is an ODBC query I just created. The Command Text is the SQL
statements. The way I normally get my SQL statements is doing the operation
manually.

1) I start the macro recorder in Excel.
2) I go to menu Data - Import External Data - New Database Query
3) Double click on the MS Access Database.
4) Select the File
5) Apply your filter you are looking for in the data base using the wizard
6) Stop Recording Macro.
7) Modify the recorded macro as required. You can extract the SQL
statements a put them into a recordset if you want instead of doing the
query. The query below makes a connection using the ODBC. You can still
makde a connection without doing the query.



With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;" & _
"DBQ=C:\TEMP\Part Log.mdb;" & _
"DefaultDir=C:\TEMP;" & _
"DriverId=25;" & _
"FIL=MS Access;" & _
"MaxBufferSize=2048;PageTimeout"), Array("=5;")),
Destination:=Range("F26"))

.CommandText = Array( _
"SELECT `1234 & 5678 parts received`.ID", _
"`1234 & 5678 parts received`.Program", _
"`1234 & 5678 parts received`.`Procurement Part Number`", _
"`1234 & 5678 parts received`.`Upscreen Part Number`")
.Name = "Query from MS Access Database_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[gab1972]

Please forgive my lack of SQL knowledge...is this a query that you
place in Access and call from Excel?

 

[Joel]

The code I gave on my last posting was from Excel using the steps on listed.
it is a query of an Access database


In stead of a query here is ADO code I got from the VBA Help in ACCESS. You
can see what is called the SQL. It is part of the OPEN statement.

' Open a connection
Set Cnxn = New ADODB.Connection
strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
"Initial Catalog='Northwind';Integrated Security='SSPI';"
Cnxn.Open strCnxn

' Open Employees Table with a cursor that allows updates
Set rstEmployees = New ADODB.Recordset
strSQL = "Employees"
rstEmployees.Open strSQL, strCnxn, adOpenKeyset, adLockOptimistic,
adCmdTable


You can use this code in Excel. To use it in Excel you need to go into the
Excel VBA window and add a Tools - Reference : and check off

Microsoft Access XX.X Object Library (choose the highest number).
and
Microsoft ActiveX Data Objects X.X Library (choose highest number)

 

[gab1972]

I really hate to be a pain...

I understand that the last bit of code gives me a connection to access
from Excel using ADO....what part is allowing me to search?

Right now, I'm only giving the option to search by permit number. The
user selects a permit number from a combobox and based on that value,
Excel is going to Access, finding the corresponding recordset and
bringing it back to Excel. What I want is to expand my search
string. So instead of limiting to a permit number...say the permit
was applied for in Sumter County (one of the counties in my state) and
the engineer was Lawson & Lawson Engineers, LLC. I want my user to be
able to type in "Sumter, Lawson" in a box and have Excel find all
recordsets in my data table matching those criteria. There might be 4
recordsets matching Sumter County and Lawson & Lawson Engineers, LLC,
but each with different permit numbers. This gives the user a bit
more flexibility in narrowing down their search if they don't know the
specific permit number.

Again, I *REALLY* appreciate all your help...I hope I'm not
frustrating you with all the questions.

 

[Joel]

The last code I posted had the following:

"Initial Catalog='Northwind';Integrated Security='SSPI';"

Inital Catalog and Integrated Security are two columns in the database.

In your database you have two columns "Permit Number" and "Engineer". Your
want

SQL = "Permit Number = '1234';Engineer = 'Lawson & Lawson Engineers, LLC'"



Now if you want to make the line variable do this

PNum = 1234
Eng = Lawson & Lawson Engineers, LLC

Then

SQL = "Permit Number = '" & PNum & "';Engineer = '" & Eng &"'"

The above line has the following in order

" - a single quote
'" - a single quote followed by a double quote
"' - a double quote followed by a single quote
'" - a single quote followed by a double quote
"'" - a double quote, single quote, and then another double quote


I hope this gets you a little futher along