Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    May 2011
    Location
    Amsterdam
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Angry Writing Excel VBA arrays to MS Access tables

    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

  2. #2
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    vancouver, BC, Br. Columbia
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    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.

  4. #4
    New Lounger
    Join Date
    May 2011
    Location
    Amsterdam
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.
    Wendell

  6. #6
    New Lounger
    Join Date
    May 2011
    Location
    Amsterdam
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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)!

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.
    Wendell

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •