Creating An ACCESS Table From A Linked Excel XLS

[Mike S. S.]

I have used code to successfully populate records into an internal ACCESS
table from an LINKED Excel file. NO PROBLEM. However, in case the
spreadsheet changed, I decided to use tabledefs to create a table from the
spreadsheet using the .Fields.Append .CreateField method and looping through
the XLS (opened as a dynaset). After deleting and recreating the table, I
begin the same routine as before to move through the rows and populate the
table I just created. And this time, instead of working perfectly as before,
it only partially populates and then ends. I have examined the rows in the
XLS around where it stops and find nothing. I cannot get any error returned
or see any problem. HELP!!!

I realize because there are so many potential variables when using a linked
XLS as the data source that it might be difficult for someone out there to
have the answer. I would be happy to send the code that works with the added
code that creates the table on the fly. I am at (e-mail address removed)

Thanks to anyone who has a clue as to what might be happening or how I might
even try to debug the situation.

 

[Alex Dybenko]

Hi,
I would try to run a make-table query to create a new table

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Mike S. S.]

Thanks Alex for responding. I use a make-table query in the first technique
I described. I added the code to create the table on the fly from the XLS so
that I would always be sure I had the exact column/field names since the
source is the linked XLS and it's column headers are out of my "control" and
could change, which would cause a problem with a make-table query.

 

[John W. Vinson]

Thanks Alex for responding. I use a make-table query in the first technique
I described. I added the code to create the table on the fly from the XLS so
that I would always be sure I had the exact column/field names since the
source is the linked XLS and it's column headers are out of my "control" and
could change, which would cause a problem with a make-table query.

That would be an argument for having a permanent Access table, with the
desired fieldnames, datatypes and sizes, and running an Append query from the
linked spreadsheet as needed, rather than going to the trouble of creating a
new table.