Excel VBA Connection to Access

[Twi78]

Hi All. I currently have a workbook that has VBA functions that calculates values by looping through large amounts of data in a few large excel sheets. I want to transfer the data from these sheets into Access and then bring data into the VBA code via an array from the data in Access rather than the array being populated from the data in excel as it currently is. However I know this is possible but have no idea how to create the connection. The access DB will be situated in a public directory on a server. Can anyone show me how to create the connection string and then how I call the data from Access? Or point me to some tutorials. I have just downloaded Office 2013 in case you need to know what version I am working in.

 

[Jon von der Heyden]

Hi

For starters read some of this tutorial on ADO. You don't have to read everything. Focus on the connection object and recordset object (see section called "Getting data").

One way to use Access is not only to store the data in it, but also the queries that you require (can be parameter queries). I wrote an article here on how to create the queries from Excel. Now you don't have to create them from Excel but the code does show how to create the connection. And what may be of further use is the part that describes how to run queries in Access.

Finally, the most common way of dumping data from an ADO recordset to a range is by using the CopyFromRecordset method, so have a look at that.

Feel free to post back with some specific details of what you are trying to do if you get stuck.

 

[Twi78]

Hi Jon,

Thanks for the reply really useful references, I have now managed to link to the data in an Access database (see code below), and populate the array using a recordset. Now the array seems to populate OK but I couldnt understand why the functions I have programmed were not running through it, so I dumped the array into an Excel sheet to see the output and it appears to have flipped the structure. That is the Rows are now columns and vice versa? Any pointers here?

Thanks
Garry

Sub FillArrayAccess()
Dim oConn As ADODB.Connection
Dim oRs As ADODB.Recordset
Dim sConn As String
Dim sSQL As String
Dim StrDBPath As String
StrDBPath = "File path here for Access DB"
sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                             "Data Source=" & StrDBPath & ";" & _
                             "Jet OLEDB:Engine Type=5;" & _
                             "Persist Security Info=False;"
' Open a connection.
Set oConn = New ADODB.Connection
oConn.Open sConn
' Make a query over the connection.
sSQL = "SELECT * FROM [Tag List with Cycle Time];"
Set oRs = New ADODB.Recordset
oRs.Open sSQL, oConn, adOpenStatic, adLockBatchOptimistic, adCmdText
DataArray = oRs.GetRows()
'show the contents of the array
Sheets("Sheet2").Range("A1:T131666").Value = DataArray

 

[Jon von der Heyden]

What do you actually want to do with the array. I hate populating arrays with recordsets for this very reason. You could try transposing the array i.e.

varArray = Transpose(varArray)

but even that might throw a Type Mismatch RT error and then you'll have to resort to looping through the array and transpose it at each iteration.

Recordsets are fantastic objects. You can filter them, sort them and do other cool things. It's often possible to achieve the desired outputs without using an array. If all you want to do is drop the recordset to a range you can use the CopyFromRecordset method.

Let me know what your actually requirement is and perhaps I can suggest a way forward.

 

[Jon von der Heyden]

Here is another MSDN article that tells you what properties, methods and events are available for recordset objects.

 

[Jon von der Heyden]

Sorry just one more thought. If you are determined to use an array (don't do it! ) then one way I have cheated was to convert it to a string variant. Using GetString method (ADO) you can specify a column delimiter. After that you can use the Split function to create an array from the string. This way it won't be transposed. I'm not entirely sure what the risks are in using this method but I have found it quite reliable for recordsets within 1000000 records.

 

[RoryA]

Given a variant array, you can also use something like this to transpose it:

    With CreateObject("New:{8BD21D20-EC42-11CE-9E0D-00AA006002F3}")
        .Column = vData
        vData = .List
    End With

Unlike Application.Transpose it will accept text longer than 911 characters and arrays larger than 65536 rows.

 

[Jon von der Heyden]

^^ Blimey what is that object??

 

[Twi78]

The original data is production data, with 21 fields and at the moment 130,000 entries. I have 12 machines that each product passes through, with 10 key performance events. I have therefore written 120 functions in Excel VBA where each function determines the machine/KPI measure with various paramters such as year/period range/product type etc. When you write a function in Excel, the function procedure loops through the data calculating a total that is determined by its parameters.

I guess if I can use the recordset to produce a value based on certain criteria this will provide the same functionality. My concern is that I used an array to save time. Originally the 120 functions when presented in a summary table, each referenced an excel sheet to iterate through to calculate its value. This took around 4 minutes to calculate. The array method took 8 seconds as I populated it on request and pointed each function to the one array. Can I still create a recordset in the module and point each function to the recordset?

 

[RoryA]

^^ Blimey what is that object??

It's an MSForms.Listbox obviously.