retrieve data from access database

[newprogamer]

Hello, I have the following code writing to an access database. I would like the users to be able to search on different criteria (for example model number , maufacturer,etc.) to retrieve the data. Does anyone know the best way to retrieve the data. Any help would be appreciated.

*********************

'Write Filename to access database
Dim db As Database
Dim Rs As Recordset

Set db = OpenDatabase("C:\Quotes\quoteDB.mdb")

' open the database
Set Rs = db.OpenRecordset("FileName", dbOpenTable)

With Rs
.AddNew 'create a new record into the database

' add values to each field in the record
Rs("QuotedBy") = strInitials
Rs("Sequence") = strSequence
Rs("Manufacturer") = strManufacturer
Rs("Model") = strModel
Rs("Name") = strFileName

.Update 'writes record to the database
End With
End Sub

 

[pbrodsky]

Where are you running the code from (what application) and how to you want to show the results to the user?

 

[newprogamer]

This code is running from excel (VBA). I would like to display a grid/table, just in case the user's search criteria has more than one result. I'm not sure where to start. I have looked in a VBA book that didn't help.

 

[PHV]

You may try MS-Query:
menu Data -> External data -> ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pbrodsky]

You could use a apreadsheet to display the data.

something like:

Dim db As Database
Dim Rs As Recordset

Set db = OpenDatabase("C:\Quotes\quoteDB.mdb")
Rs=db.execute("select * from filename where ...")
Rs.movefirst
irow=3
do while not Rs.eof
cells(irow,1)=Rs("QuotedBy")
cells(irow,2)=Rs.("Sequence")
....
irow=irow+1
Rs.movenext
loop

There are probably neater ways to do it, but something like this should work.

There are examples of most of what's needed in the Excel VBA help file -- I refer to them freqently when I need to do something like this, which I do fairly often, but not often enough to remember all the syntax.

 

[PHV]

You may also consider the CopyFromRecordset method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[newprogamer]

Thanks, I'll them over the week-end.

 

[newprogamer]

Hello Everyone,

I tried pbrodsky's code above. But, I get a compile error
at db.Execute. Any ideas or suggestions. Please help!
I created a form with 2 combo boxes and a search command button. **********************************************************
Private Sub cmdSearch_Click()
Dim db As Database
Dim Rs As Recordset

'open the database
Set db = OpenDatabase("C:\Quotes\quoteDB.mdb")

'open the record set and search for quoted by that equals the text in the cbo box
Rs = db.Execute("select * FROM filename WHERE QuotedBy = cboQuotedBy.Text")
Rs.MoveFirst
irow = 3
Do While Not Rs.EOF
Cells(irow, 1) = Rs("Quoter")
Cells(irow, 2) = Rs("Initials")
Cells(irow, 3) = Rs("Sequence")
Cells(irow, 4) = Rs("Manufacturer")
Cells(irow, 5) = Rs("Model")
Cells(irow, 6) = Rs("Name")
Cells(irow, 7) = Rs("DateQuoted") 'date generated by access

irow = irow + 1
Rs.MoveNext
Loop
End Sub

 

[mrmovie]

("select * FROM filename WHERE QuotedBy = cboQuotedBy.Text")

i think might need to be

("select * FROM filename WHERE QuotedBy = " & cboQuotedBy.Text)

 

[newprogamer]

mrmovie, I made the change you suggested.

I still receive a 'Compile Error: Expected Function or Variable' and the code will stop executing and higlight db.execute.

 

[RoyVidar]

I think the .execute method of the dao database is best used for executing action queries. Similar would work for ADO, though (set rs=currentproject.connection.execute(strsql)).

Use the openrecordset method in stead:

[tt]set Rs = db.openrecordset("select * FROM filename WHERE QuotedBy = " & cboQuotedBy)[/tt]

Roy-Vidar

 

[newprogamer]

Thanks, it does not stop at the db.execute. RoyVidar your suggestion works great.

Now I am receiving an error. Error number 3061. Too few parameters. I quite understand the irow or if this is the best way to retrieve data from access and display the results for the user. But I'm trying it. I would like the user to be able to search for the Manufacturer, model, or Quoters' name and see a list and then select the corresponding file he/she needs.

My code is below. Please help!

***********************************************************
Private Sub WriteToDB(strQuoter, strInitials, strSequence, strManufacturer, strModel, strFileName)

'Write Filename to access database
Dim db As Database
Dim Rs As Recordset

Set db = OpenDatabase("C:\Quotes\quoteDB.mdb")

' open the database
Set Rs = db.OpenRecordset("tblFileName", dbOpenTable)

With Rs
.AddNew 'create a new record into the database

' add values to each field in the record
Rs("Quoter") = strQuoter
Rs("Initials") = strInitials
Rs("Sequence") = strSequence
Rs("Manufacturer") = strManufacturer
Rs("Model") = strModel
Rs("Name") = strFileName

.Update 'writes record to the database
End With
End Sub
'**********************************************
Private Sub cmdSearch_Click()
Dim db As Database
Dim Rs As Recordset
Dim irow As String

On Error GoTo errhandler:

'open the database
Set db = OpenDatabase("C:\Quotes\quoteDB.mdb")

'open the RS and search for contents in the cbo box
Set Rs = db.OpenRecordset("SELECT * FROM tblFileName WHERE Manufacturer = " & cboManufacturer.Text)

Rs.MoveFirst
irow = 8
Do While Not Rs.EOF
Cells(irow, 1) = Rs("Quoter")
Cells(irow, 2) = Rs("Initials")
Cells(irow, 3) = Rs("DateQuoted") 'date generated by access
Cells(irow, 4) = Rs("Sequence")
Cells(irow, 5) = Rs("Manufacturer")
Cells(irow, 6) = Rs("Model")
Cells(irow, 7) = Rs("Name")

irow = irow + 1
Rs.MoveNext
Loop

errhandler:
MsgBox Err.Number & " " & Err.Description & "."

End Sub