Export from Excel or Import into Access? Which is better?

[ryguy7272]

I am looking for some advice for a method of exporting data from Excel to
Access, or perhaps importing data into Access from Excel. I am trying to
dertermine which method is better.

I tried both of these methods:
http://www.erlandsendata.no/english/index.php?d=envbadacexportado
http://www.erlandsendata.no/english/index.php?d=envbadacexportdao

I got both working, but it seems like the Access table that the data goes to
can't be formatted any particular way. For instance, to send the data to
Access, I had to change the data types to Text, with 250 characters, and if I
used AutoNumber, and ran through the process several times, the AutoNumber
would keep counting consecutively, and eventually it grew pretty high. I
have about 2,000 records in Excel, but the AutoNumber ID in Access was
showing over 10,000 records after running through the process five times.
This didn't seem to serve the purpose (anyway, it didn't seem to make sense).
I think if the number, which I was using as an ID, could be reset each time,
it would make a lot more sense. Is it possible to reset this AutoNumber?
Also, I had to manually delete the data each time before running the Excel
macro, or the data woudl just get pushed to the bottom of the table each time
I did another export from Excel to Access. Ideally, I would like to delete,
clear, or somehow overwrite the existing data in my one table, named tblTFI,
because the data from the Excel sheet is refreshed constantly, and I just
wanted to get the data in Access for some (relatively) advanced queries and
reports.

I had one other issue when I did the import. It seemed to wreak havoc on
two of my forms, which I set up (with tremendous help from some contributors
to this DG) specifically for creating dynamic queries and reports on the fly.

Please see this link:
http://www.microsoft.com/office/com...osoft.public.access.modulesdaovba&fltr=&exp=1
Titled:
Seeking Help With VBA and SQL Instant Query

The forms worked fine initially, and created fantastic queries and, but when
I sent the new data from Excel to the tblTFI, the Forms became very
unstable!! Now, sometimes the queries and reports will work and sometimes
they won't work (getting balnks in the ComboBoxes). I must be doing
something wrong. I believe this is quite an easy porcess, but I am not
quite proficient with Access. Some people must import/export data between
Excel and Access all day long, right. I just can't seem to get it working
for some reason. I have a few books, which have helped quite a lot, but the
books don't go into any great detail about using VBA to delete data in a
table, import fresh data, and create dynamic queries and reports...and this
is essentially what I want to do.

Basically, how do I clear data in a table, and preserve the column headings?
Also, should I export from Excel to Access or import from Excel into Access?
I really want to preserve the integrity of the data for my two Forms to be
able to create my two types of queries. Finally, isn't there some type fo
macro called 'TransferSpreadsheet' may serve the purpose? Would that be an
option to do this more eloquently than the way that I am proposing to do it?
I have a lot of experience working with Excel, but my experience with Access
is somewhat limited. Please help.

Regards,
Ryan---

 

[john.fuller]

I am looking for some advice for a method of exporting data from Excel to
Access, or perhaps importing data into Access from Excel.  I am trying to
dertermine which method is better.

I tried both of these methods:http://www.erlandsendata.no/english...data.no/english/index.php?d=envbadacexportdao

I got both working, but it seems like the Access table that the data goes to
can't be formatted any particular way.  For instance, to send the data to
Access, I had to change the data types to Text, with 250 characters, and if I
used AutoNumber, and ran through the process several times, the AutoNumber
would keep counting consecutively, and eventually it grew pretty high.  I
have about 2,000 records in Excel, but the AutoNumber ID in Access was
showing over 10,000 records after running through the process five times.  
This didn't seem to serve the purpose (anyway, it didn't seem to make sense).
 I think if the number, which I was using as an ID, could be reset each time,
it would make a lot more sense.  Is it possible to reset this AutoNumber?  
Also, I had to manually delete the data each time before running the Excel
macro, or the data woudl just get pushed to the bottom of the table each time
I did another export from Excel to Access.  Ideally, I would like to delete,
clear, or somehow overwrite the existing data in my one table, named tblTFI,
because the data from the Excel sheet is refreshed constantly, and I just
wanted to get the data in Access for some (relatively) advanced queries and
reports.

I had one other issue when I did the import.  It seemed to wreak havoc on
two of my forms, which I set up (with tremendous help from some contributors
to this DG) specifically for creating dynamic queries and reports on the fly.

Please see this link:http://www.microsoft.com/office/community/en-us/default.mspx?pg=2&cat...
Titled:
Seeking Help With VBA and SQL Instant Query

The forms worked fine initially, and created fantastic queries and, but when
I sent the new data from Excel to the tblTFI, the Forms became very
unstable!!  Now, sometimes the queries and reports will work and sometimes
they won't work (getting balnks in the ComboBoxes).  I must be doing
something wrong.  I believe this is quite an  easy porcess, but I am not
quite proficient with Access.  Some people must import/export data between
Excel and Access all day long, right.  I just can't seem to get it working
for some reason.  I have a few books, which have helped quite a lot, butthe
books don't go into any great detail about using VBA to delete data in a
table, import fresh data, and create dynamic queries and reports...and this
is essentially what I want to do.  

Basically, how do I clear data in a table, and preserve the column headings?
 Also, should I export from Excel to Access or import from Excel into Access?
 I really want to preserve the integrity of the data for my two Forms tobe
able to create my two types of queries.  Finally, isn't there some type fo
macro called 'TransferSpreadsheet' may serve the purpose?  Would that bean
option to do this more eloquently than the way that I am proposing to do it?  
I have a lot of experience working with Excel, but my experience with Access
is somewhat limited.  Please help.

Regards,
Ryan---

I would need more info, but if it is the same excel file everytime,
just link to the excel file as a linked table. Also, if the data in
the excel file is refreshed constantly, where does this data come
from? Most excel files that refresh like i'm thinking are usually
just linked to a SQL query on a server somewhere, and thus you may be
able to skip the linking to the excel file completely.

 

[ryguy7272]

Oh my goodness!! When I read your post I immediately knew what to do. I was
really over thinking this thing!! This was a great, and SIMPLE, solution.
Now everything works out fine. I just can’t figure out one thing…why didn’t
I think if that before I wrote up that superfluous message and posted it. Oh
well. It really helps to have another pair of eyes on these things.


Regards,
Ryan--