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.

--
--
Thanks

Parkin_m

Take a look he

http://support.microsoft.com/default.../excel/dao.asp

One of the article links there shows you how

--
Regards,
Tom Ogilvy


"parkin_m" wrote:

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.

--
--
Thanks

Parkin_m

this is the specific article:

http://support.microsoft.com/kb/149254/en-us

Dim rs As Recordset
Dim varrecords As Variant
Dim x as Long
rs.MoveLast
rs.MoveFirst
x = rs.RecordCount
varrecords = rs.GetRows(x)

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

Take a look he

http://support.microsoft.com/default.../excel/dao.asp

One of the article links there shows you how

--
Regards,
Tom Ogilvy


"parkin_m" wrote:

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.

--
--
Thanks

Parkin_m