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...

cn.Open CurrentProject.Connection

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:

    Dim cn As New ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim rs2 As ADODB.Recordset
     
    cn.Open CurrentProject.Connection
     
    Set rs = New ADODB.Recordset
     
    rs.Open "table or SELECT...", cn, adOpenDynamic (or blank if forward-only), adLockOptimistic (or blank if read-only)
     
    'If I'm opening more than one recordset which is often the case
     
    Set rs2 = New ADODB.Recordset
     
    rs2.Open "table or SELECT...", cn, adOpenDynamic (or blank if forward-only), adLockOptimistic (or blank if read-only)
     
    'rest of code
     
    rs.Close
    Set rs = Nothing
    rs2.Close
    Set rs2 = Nothing
    cn.Close

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.

Sub PullFromAccess()

Dim LstObject As ListObject
Dim strPath, sqlString, ConnectionString As String
Dim rs, conn As Object
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.RecordSet")
Dim rCell, RngSource As Range
Dim CounterEvery2 As Integer
Dim CustomerVar, CustomerClause As String

CustomerVar = Worksheets("Customer Information").Cells(2, 6).Value

Set wksConf = Worksheets("Configuration Model")

strPath = "E:\Makro\Database Integration\Changing Relationships\Model Excel-Database Relationships v32.accdb"

Set RngSource = Sheets("Queries Source").ListObjects("t_rngsource").ListColumns("TableName").DataBodyRange

ConnectionString = "Provider = Microsoft.ACE.OLEDB.12.0; data source=" & strPath
conn.Open ConnectionString

wksConf.UsedRange.Clear

CustomerClause = " WHERE CustomerName='" & CustomerVar & "'"

lRow = 1

For Each rCell In RngSource

    ''Added specific customer condition
    
    If rCell.Offset(0, 2) = "Yes" And InStr(1, rCell.Offset(0, 1), "yC_") > 1 Then
        sqlString = Replace(rCell.Offset(0, 1), "yC_", "y" & CustomerVar & "_")
    ElseIf rCell.Offset(0, 2) = "Yes" Then
        sqlString = rCell.Offset(0, 1) & CustomerClause
    Else
        sqlString = rCell.Offset(0, 1)
    End If

    With wksConf
    
      CounterEvery2 = CounterEvery2 + 1

      Set rs = conn.Execute(sqlString)
      Set LstObject = .ListObjects.Add(SourceType:=xlSrcQuery, Source:=rs, _
      Destination:=.Cells(lRow, 1))
      
      LstObject.Name = rCell
    
      LstObject.QueryTable.Refresh BackgroundQuery:=False
      
      If Not CounterEvery2 Mod 2 = 1 Then
            LstObject.TableStyle = "TableStyleMedium3"
      End If
      
      lRow = lRow + LstObject.Range.Rows.Count + 1
      
      Set rs = Nothing
      LstObject.Unlink
      Set LstObject = Nothing
                    
    End With
    
Next rCell

MsgBox "macro done!"


End Sub

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