Writing Excel VBA arrays to MS Access tables

Topic

New Reply

Hey Guys,

I have been struggling with the following problem:
I have a Excel file called ‘A’ which contains 53 tabs with three columns of data (250000 rows) in each. These data are the results of simulation, written from Excel array to array range. But since it takes awful long to save and work with large Excel file, I would like to print each VBA array directly to MS access tables (access file is called ‘DikesSim’).

It would be great if I could create new tables in access with headers when writing Excel array to Access.

I tried this in many ways but nothing works.

I have Excel and Access 2010!

Thanks a lot,

Regards,
Giri

Reply | Quote

Replies

I would start by just manually pasting the data into an Access table and then writing VBA code in Access which makes a table and populates it from the “pasted” table. Once that works, then it’s a matter of using ‘automation’ (or whatever its called these days) to run Access VBA code from Excel that does the same thing except the new table is populated from the array which you pass to Access. Have you tried something like this yet?

Reply | Quote

You could write automation code in Access to import one tab at a time into a new table then use an append query to populate the one table in Access if that is what you want.

Reply | Quote

Hey guys,

Thanks for your reactions! I could do it manually but I think it is better to automize as I have to run simulation (it creates each time about 1GB data) various times with varios varameters. After the simulation process I perform data analysis, which is very difficult as it is a huge file. It takes several minutes if I want to save it, it is simply not possible to do all this work in one file.

I am trying to extend the code I found on internet, but I have no experience at all with Access VBA:

Sub CreateTable()
‘Add Reference to Microsoft ActiveX Data Objects 6.x Library
Dim strConnectString As String
Dim objConnection As ADODB.Connection
Dim strDbPath As String
Dim Cn As ADODB.Connection
Dim oCm As ADODB.Command
‘Set database name and DB connection string——–
strDbPath = “F:DikesData.accdb”
strConnectString = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & ThisWorkbook.Path & strDbPath & “;”
‘==================================================
‘Connect Database; insert a new table
Set objConnection = New ADODB.Connection
With objConnection
.Open strConnectString
.Execute “CREATE TABLE MyTable ([EmpName] text(50) WITH Compression, ” & _
“[Address1] text(150) WITH Compression, ” & _
“[Address2] text(150) WITH Compression, ” & _
“[City] text(50) WITH Compression, ” & _
“[State] text(2) WITH Compression, ” & _
“[PIN] text(6) WITH Compression, ” & _
“[SIN] decimal(6))”
End With

Set objConnection = Nothing

End Sub

Reply | Quote

With that amount of data, I’m not sure that Access is what you want to use. If you are creating 1 GB of data with each simulation, you are pushing the limit of an ACCDB format database which is 2 GB. You could get around that limitation by using a different file each time, but if you are wanting to do analysis across various simulations, that gets rather cumbersome. Are you creating the simulation in VBA in Excel? If so, you might want to store the data in a SQL Server database, which can be much larger. I should add however that the analysis tools in both Access and SQL Server are quite different from those you have in Excel.

Reply | Quote

Hi WendellB,

Thanks for your reply. Indeed I was also looking for somekind of DB system, but I have no idea I could do with SQL. The simulation is done in Excel VBA which is quite quick (since I use only array, no cell reference or whatsoever). I also opt for Matlab but it seems that it is not quicker than the VBA in Excel.
I need to be able to store data which I can acquire easily from Excel (not manually)!

Reply | Quote

The Express version of SQL Server is a free download, and includes some tools for importing and exporting data to/from Excel. However any database product will take considerable time importing 250000 rows of data. All of the Excel work is generally done in RAM, so accessing and storing data happens very quickly. Database products all store the rows to disk, which takes substantially longer. Hope this helps.

Reply | Quote