Import data from Excel to Access

[SyafiqIzzat]

Good Day,

I have difficulty in doing my database project. I have an excel workbook where users enter all the data in the cells. Later, from Access I need to import all the data that I have filled earlier in Excel. I know we would need to use Import wizard. It is tedious to do so moreover if the data in Excel is updated. So, I'm looking for help how to import the data from Excel to Access using VBA. Really looking for feedbacks.

TIA.

 

[the_net_2.0]

how about linking the two? why is importing tedious? it shouldn't be...

 

[SyafiqIzzat]

Because there are many sheets in the Excel and whenever I update the data, it needs to be done by using the Import Wizard. I want to automate this part. I've tried linking the tables, it is not the best option since in Access the linked table's design can't be modified. I will need to select primary key n all. Link does not permit modifying design view in Access, too bad.

 

[the_net_2.0]

I'm sorry but you're not making much sense here. What exactly is the issue? Getting the updated information into Access from Excel? If that is what you're after, you can simply drop the old table before importing the new one. That is one simple way to get updated information into Access with the same table name.

 

[SyafiqIzzat]

If I need to update 10 tables, so I have to drop 10 tables and go on with the import wizard like 10 times?
I prefer to have a button in Excel whereby after you update the table and click the button, it would be added in Access automatically. I want it to be so and I know it is possible as I found few examples in the internet.

 

[the_net_2.0]

anything is possible, and this is too. but to take a different approach, why does the data need to be in Access? what's the purpose?

a lot of times people come and ask for help on forums and the best answer really has nothing to do with Access or any other database technology, because the need that they have can be accomplished with other technologies, or none at all.

a good example of that would be someone who accepts and Access project from a supervisor, but doesn't realize that Access is not even needed to accomplish what the supervisor has laid out as the assignment.

 

[SyafiqIzzat]

Well, you got point there. It is actually because of supervisor. I am an intern student and he wants me to come out with a project whereby he wants the user to insert data in Excel and the other processes would take part in Access. I try to convince him to use Access fully but somehow he wants the system to be that way. So, do you have any idea of solutions on how to export data to access?

 

[the_net_2.0]

If I need to update 10 tables, so I have to drop 10 tables and go on with the import wizard like 10 times?
I prefer to have a button in Excel whereby after you update the table and click the button, it would be added in Access automatically. I want it to be so and I know it is possible as I found few examples in the internet.

And again, I will say that this is the best. If you're the intern, best probably not to argue the "right" point. Just do what you're told, huh?

The point is still not clear. Lets assume you need to import all 10 sheets from one workbook everyday such that you will end up with 10 different tables. What you could do is run an import wizard on those 10 table manually, one time each, and save those import routines in the "save imports" option in Access. Say for example, you named the imports 1-10. After the first time then, what you could do is run an AutoExec macro that runs a small sub or function everytime the database is opened to get the new data in before the user has a chance to modify it. such a piece of code would be something like:

public sub prepNewData()

on error resume next

dim tbls(9) as string
dim db as database
dim i as long

set db = currentdb()

tbls(0) = TABLE1
tbls(1) = TABLE2
tbls(2) = TABLE3
tbls(3) = TABLE4
tbls(4) = TABLE5
tbls(5) = TABLE6
tbls(6) = TABLE7
tbls(7) = TABLE8
tbls(8) = TABLE9
tbls(9) = TABLE10

for i = 0 to 9
   db.tabledefs.delete tbls(i)
next i

for i = 1 to 10
   docmd.runsavedimportexport cstr(i)
next i

refreshdatabasewindow

set db = nothing

end sub

does that make sense? there are other ways to do it obviously, but this will work fine for your purposes.