Getting fields from DAO database and placing them in variables

P

parkin_m

I have a microsoft access database (.mdt) which I am pulling data from into
excel. The idea is that I have a list of products in the database which are
pulled into a popup box userform in excel - the user selects how many of each
item and this is placed into the worksheet and the prices etc are added to
form an invoice.

What I currently have:

'----------------------------------------------------------------
Sub newConnection()
DAOCopyFromRecordSet "D:\Access\masterdatabase.mdb", _
"Products", "Description", Range("A47")
End Sub
'----------------------------------------------------------------

Sub DAOCopyFromRecordSet(DBFullName As String, TableName As String, _
FieldName As String, TargetRange As Range)
Dim db As Database, rs As Recordset
Dim intColIndex As Integer
Set TargetRange = TargetRange.Cells(1, 1)
Set db = OpenDatabase(DBFullName)
Set rs = db.OpenRecordset(TableName, dbOpenTable) ' all records
'Set rs = db.OpenRecordset("SELECT description FROM " & TableName &
dbReadOnly) ' filter records
' write field names
For intColIndex = 0 To rs.Fields.Count - 1
TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
Next
' write recordset
TargetRange.Offset(1, 0).CopyFromRecordset rs
Set rs = Nothing
db.Close
Set db = Nothing
End Sub
'--------------------------------------------------------------------------

This is allowing me to pull all of the fields from the description row, (i
understand how to do SQL queries) but I cannot work out how to get into each
of the single fields and store them in an array. If someone could help then
that would be great.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top