Import Data from Access via DAO

[LHC01]

I am working with Office XP:

- Access database named Sales.mdb (at c:\data\) with a table tblSales
table tblSales has the following structure (about 70 fields):
RcdInd Customer Field1 Field2 Field3 Field4 Field5 Field6 ..

It contains data for 200 Customers and each Customer has 4 records
(RcdInd = 01, 02, 03 and 04)

- Excel workbook SalesD.xls (at c:\data\xlslib\)
This workbook is being used as a template i.e. it is formatted to
reflect data for each Customer.

I have started to write VBA in Excel using DAO to connect to Access

The following is an excerpt from my code:

' Open workbook SalesD.xls
ChDir "C:\DATA\XLSLIB\"
Workbooks.Open FileName:="SalesD.xls"

' Select the MS Access Database that houses the data
Dim DB As Database, RS As Recordset
' Open the Sales.mdb database
Set DB = OpenDatabase("c:\data\sales.mdb")

' Generate Recordset via an SQL query agains table tblSales
Set RS = DB.OpenRecordset(" SELECT Field1, Field2, Field3 " & _
" FROM tblSales " & _
" WHERE RcdInd = '01' AND Customer = '100047-A';")
' Copy recordset onto the worksheet, stating at cell C2
[C2].CopyFromRecordset RS

' Generate Recordset via an SQL query agains table tblSales
Set RS = DB.OpenRecordset(" SELECT Field6, Field7, Field8 " & _
" FROM tblSales " & _
" WHERE RcdInd = '02' AND Customer = '100047-A';")
' Copy recordset onto the worksheet, stating at cell F28
[F28].CopyFromRecordset RS
............................................................
............................................................
This code works fine; however, it is cumbersome as the Customer number
must be changed each time.

Is it possible to do the following? (if so, how?)

1. Perform the above for each Customer, save the output as
a new workbook.
2. Continue to process each Customer as above.

Something like:
For i = 1 to 200
code (SQL and CopyFromRecordset routines) .......
Next


Best Regards

 

[Jamie Collins]

(e-mail address removed) (LHC01) wrote ...

- Access database named Sales.mdb (at c:\data\) with a table tblSales
table tblSales has the following structure (about 70 fields):
RcdInd Customer Field1 Field2 Field3 Field4 Field5 Field6 ..

It contains data for 200 Customers and each Customer has 4 records
(RcdInd = 01, 02, 03 and 04)

- Excel workbook SalesD.xls (at c:\data\xlslib\)
This workbook is being used as a template i.e. it is formatted to
reflect data for each Customer.

I have started to write VBA in Excel using DAO to connect to Access

The following is an excerpt from my code:

' Open workbook SalesD.xls
ChDir "C:\DATA\XLSLIB\"
Workbooks.Open FileName:="SalesD.xls"

' Select the MS Access Database that houses the data
Dim DB As Database, RS As Recordset
' Open the Sales.mdb database
Set DB = OpenDatabase("c:\data\sales.mdb")

' Generate Recordset via an SQL query agains table tblSales
Set RS = DB.OpenRecordset(" SELECT Field1, Field2, Field3 " & _
" FROM tblSales " & _
" WHERE RcdInd = '01' AND Customer = '100047-A';")
' Copy recordset onto the worksheet, stating at cell C2
[C2].CopyFromRecordset RS

' Generate Recordset via an SQL query agains table tblSales
Set RS = DB.OpenRecordset(" SELECT Field6, Field7, Field8 " & _
" FROM tblSales " & _
" WHERE RcdInd = '02' AND Customer = '100047-A';")
' Copy recordset onto the worksheet, stating at cell F28
[F28].CopyFromRecordset RS
............................................................
............................................................
This code works fine; however, it is cumbersome as the Customer number
must be changed each time.

Is it possible to do the following? (if so, how?)

1. Perform the above for each Customer, save the output as
a new workbook.
2. Continue to process each Customer as above.

Something like:
For i = 1 to 200
code (SQL and CopyFromRecordset routines) .......
Next

As an alternative to using CopyFromRecordset, consider using either
the INSERT INTO..SELECT syntax (where the Excel table already exisits)
or the SELECT..INTO syntax (creates the Excel table and even creates
the workbook if it doesn't already exist). Such Jet syntax operates on
closed workbooks and consequently executes much faster.

I am working with Office XP

As an alternative to using DAO, consider using the more modern ADO.
You'll find more people in these ngs use ADO and find more support
generally.

Jamie.

--

 

[LHC01]

(e-mail address removed) (LHC01) wrote ...

- Access database named Sales.mdb (at c:\data\) with a table tblSales
table tblSales has the following structure (about 70 fields):
RcdInd Customer Field1 Field2 Field3 Field4 Field5 Field6 ..

It contains data for 200 Customers and each Customer has 4 records
(RcdInd = 01, 02, 03 and 04)

- Excel workbook SalesD.xls (at c:\data\xlslib\)
This workbook is being used as a template i.e. it is formatted to
reflect data for each Customer.

I have started to write VBA in Excel using DAO to connect to Access

The following is an excerpt from my code:

' Open workbook SalesD.xls
ChDir "C:\DATA\XLSLIB\"
Workbooks.Open FileName:="SalesD.xls"

' Select the MS Access Database that houses the data
Dim DB As Database, RS As Recordset
' Open the Sales.mdb database
Set DB = OpenDatabase("c:\data\sales.mdb")

' Generate Recordset via an SQL query agains table tblSales
Set RS = DB.OpenRecordset(" SELECT Field1, Field2, Field3 " & _
" FROM tblSales " & _
" WHERE RcdInd = '01' AND Customer = '100047-A';")
' Copy recordset onto the worksheet, stating at cell C2
[C2].CopyFromRecordset RS

' Generate Recordset via an SQL query agains table tblSales
Set RS = DB.OpenRecordset(" SELECT Field6, Field7, Field8 " & _
" FROM tblSales " & _
" WHERE RcdInd = '02' AND Customer = '100047-A';")
' Copy recordset onto the worksheet, stating at cell F28
[F28].CopyFromRecordset RS
............................................................
............................................................
This code works fine; however, it is cumbersome as the Customer number
must be changed each time.

Is it possible to do the following? (if so, how?)

1. Perform the above for each Customer, save the output as
a new workbook.
2. Continue to process each Customer as above.

Something like:
For i = 1 to 200
code (SQL and CopyFromRecordset routines) .......
Next

As an alternative to using CopyFromRecordset, consider using either
the INSERT INTO..SELECT syntax (where the Excel table already exisits)
or the SELECT..INTO syntax (creates the Excel table and even creates
the workbook if it doesn't already exist). Such Jet syntax operates on
closed workbooks and consequently executes much faster.

I am working with Office XP

As an alternative to using DAO, consider using the more modern ADO.
You'll find more people in these ngs use ADO and find more support
generally.

Jamie.

--

Thank you for the recommendations regarding the use of INSERT TO .. SELECT
and DAO.
Any suggestions regarding my main issue?
- Open workbook SalesD.xls
- Connect to Access
- Import data from table tblSales for the first Customer into workbook
SalesD.xls, save the workbook e.g. Book1.xls.
- Perform the same process with the next Customer.
- Repeat process up to 200 Customers.

Thanks again.

 

[Jamie Collins]

(e-mail address removed) (LHC01) wrote ...

Thank you for the recommendations regarding the use of INSERT TO .. SELECT
and DAO.

I recommended ADO!

Any suggestions regarding my main issue?

- Open workbook SalesD.xls

Record a macro, edit the code.

- Connect to Access

Using ADO or DAO? Google it e.g.

http://groups.google.com/groups?q="jamie+collins"+OR+onedaywhen+"OLEDB.4.0"+-"Excel+8.0"

- Import data from table tblSales for the first Customer into workbook

You have already posted your code to do this.

- save the workbook e.g. Book1.xls.

Record a macro, edit the code.

- Perform the same process with the next Customer.
- Repeat process up to 200 Customers.

You are pretty much already there with your For..Next loop. On each
iteration, replace the 100047-A value with the next customer ID (or
whatever).

My suggestions aren't very specific because, as I said before, I'd
recommend a different approach. In full, it is a lot of code to write
for a ng post and anyhow I suspect you may be able to write most if it
yourself with a little help from the macro recorder <g>.

Jamie.

--