64 Bit Excel, VBA with Access database

I have a huge Excel workbook application containing lots of data that can actually go into a database.

I was thinking of making an Access Database with a few tables that can hold such data, transfer the data to the database tables and then whenever data is needed go and read it from the database instead of finding it in the different worksheets.

It's Excel 64Bit with VBA that must access the database.

Any pointers to a good reference that can give me an example of what needs to be referenced in VBA, how to create the connection, how to create a recordset etc.

I have no experience with accessing a database from VBA code.

My problem is the way some of that data is represented in the worksheets. There will be no way to just import the data.

I will have to create a database. Then connect to it and loop the data importing each piece of data into the database.

I have looked at some examples I found googling but for instance...

Please Login or Register  to view this content.

What does this mean? It doesn't show how to get to this. Where is the connection made etc.

Hi Bezzie,

change your approach. First create access database, move your data and next read this trough Excel.

Best,
Jacek

The data sits in worksheets. To get it into the database I need to open a database in VBA and import the data from the worksheets to the database using macros.

As I said the data is not just records in the worksheet. For instance: Share prices worksheet has the shares listed in rows of the worksheet while the prices is listed in the columns. Each column has a date at the top and these columns run from 1/1/2014 up to today. That's over 1800 columns.

Other worksheets e.g. accounts has few columns for each account and the data is in the rows down. But then there is multiple accounts spread over the columns on the same worksheet where some has few records and others has lots of records.

Once I can open the database and read/write to it I think I will be able to do some macros that can read the data from the worksheets and write it to the database tables in the format needed in the tables.

But before I can try this I need to get the database opened from within VBA.

Some of the examples I found is VB. Will the VB code work in VBA? Keeping in mind it has to work in Excel 2010 64bit and most probably Office 365 (64Bit?) in the near future.

I will have to experiment how to accomplish this but in order to try I need the database opened first. And that's where I'm stuck. The examples I did find all start something like this:

Please Login or Register  to view this content.

How does this piece of code know where and what the database is?

Still i am thinking best is to manually copy all data from Excel to Access.

And you can write queries in Access to do calculations. Afterall, you can connect to access and download specific queries.
Writing into Access from Excel can cause a lot of issues with performance. I was experincing that...

Best,
Jacek

Exactly,

first set up properly data in Excel, then export it to Access.
Recording macro will not work.

Best,
Jacek

For now lets forget how to get the data into the tables.

For now I need help to setup the connection and open the database. If I can do that I can start to experiment on the rest.

Please Login or Register  to view this content.

try code similar like here to connect to database.

Thanks. Will look into your example a little later. Got some other work I have to attend to now.

The first thing you need to do is understand relational database design and normalisation. Only then should you look at importing data, your data is laid out incorrectly for a database and you need to transpose it.

OK. Got the connection to the database working. Now need to learn getting the correct info from the data tables.

Battling to get the correct dataset using dates