DAO rs

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

Hi all,

I may have mistaken this, originally I thought it was possible to a DAO
recordset to read in a few thousand rows of records directly from Excel.
But as I research a bit, I am not quite sure of that any more.

I want my application to open the Excel file, create a recordset and then
start reading in the records from a worksheet. And then finally open an
Access database and append the recordset to an existing table. Where I am
stuck is: How can I create a DAO recordset without associating it to a
query or table in an Access database? Thanks for any suggestions/ code
snippets.

Ben
 
Sorry Ben, that's a no-no. You can do that with an ADO
recordset, but DAO assumes that the recordset is always
bound to a database query or table. That's deliberate
since Access isn't a server type database (like SQL
Server, for instance) and DAO isn't designed to handle
data in the same way.

I presume you are doing some validation or manipulation
prior to storing the data. My normal way of doing that
is to use the TransferSpreadsheet method to get the data
into a holding tale and then work with it from there.
The help files have a good explanation of
TransferSpreadsheet, using it either in macros or VB
modules.

Keith
 
Keith,

That's a great idea. Thanks for the suggestion. Just out of curiosity, if
I was to use ADO to read in data from Excel, how would I then append it to
an Access 97 table? Thanks again.

Ben
 
Here's some skeleton code for looping through records of an Excel file:

Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("C:\Temp.xls", False, True, "Excel 5.0")
Set rs = db.OpenRecordset("Sheet1$")

Do Until rs.EOF
' Add your code here.
' .
' .
' .

rs.MoveNext
Loop


This assumes the records start in cell A1 of worksheet named "Sheet1" in an
Excel file named "C:\Temp.xls", and that the first row (A) contains the
field names.

If all you want to do is append records from the Excel file into an Access
table, it might be easier and faster to :

1. Import or link the Excel file (e.g., via TransferSpreadsheet) and append
the records from the imported or linked table into the target table using an
append query,

2. Or, Use the IN clause in your append query to specify the Excel file
containing the records you want to append, as in something like:

INSERT INTO
[Your Table]
(
[Field1],
[Field2]
)
SELECT
[Field1],
[Field2]
FROM [Sheet1$]
IN "C:\Temp.xls" "EXCEL 5.0;"
 
Hi Ben,

Another option would be to link the excel file directly
into access and then you could use the DAO method to run
through the records and update your other table. Go to
the VB editor and type in "append using dao". This should
get you on the right track.

Jen
 
Thank you all, for your helpful suggestions. You gotta love the
newsgroup.
Click to expand...

You can also use the IN clause in the SQL (examples from A2000 help):

A Microsoft Excel worksheet
SELECT CustomerID, CompanyName
FROM [Customers$]
IN "c:\documents\xldata.xls" "EXCEL 5.0;"
WHERE CustomerID Like "A*"
ORDER BY CustomerID;

A named range in a worksheet
SELECT CustomerID, CompanyName
FROM CustomersRange
IN "c:\documents\xldata.xls" "EXCEL 5.0;"
WHERE CustomerID Like "A*"
ORDER BY CustomerID;


Hope that helps


Tim F
 
Back
Top