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]

Can I still create a recordset in the module and point each function to the recordset?

Not without changing the functions to look at a recordset. And if you are looping through it then sure I think that the array is quicker. It also seems like you have done all the work to process arrays so in this instance I take back what I said and suggest you continue with the array route. Perhaps you can narrow down the recordset first using the recordset Filter function.

Anyway I think you've been given some good suggestions on how to populate an array with a recordset. Rory's "obviously a MSForms.Listbox" method is cracking and no doubt the best way forward.

 

[anvg]

Maybe to add data from recordset in such a manner is simple a little

oRs.Open sSQL, oConn, adOpenStatic, adLockBatchOptimistic, adCmdText

Sheets("Sheet2").Range("A1").CopyFromRecordset oRs</pre>

 

[Twi78]

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.

OK, I think this will need breaking down for me here. Going bald from scratching my head so much!

 

[Jon von der Heyden]

So you populate the array as you did, using GetRows(). Say your array is called vData, the code Rory provided will transpose it so now you can pass it to your function for processing.

 

[Twi78]

Thanks, I tried that and it worked apart form I have some issues with values, converting them from decimal to single so all my values are rounded?

Can I ask, If I opened a connection to Access in the module, and used the Sql string to sum up the values where it met a certain criteria defined in each of the 120 functions, would there be any performance improvements? That is instead of calling all the data into an array, then looping through the data with 120 functions?

I am also thinking that instead of having 120 functions (10 measurs for 12 machines), I can have 10 functions and introduce an extra paramter for machine.

 

[Jon von der Heyden]

Thanks, I tried that and it worked apart form I have some issues with values, converting them from decimal to single so all my values are rounded?

I'm not sure what the culprit is here. It may be that you need to use a coercion function in your SQL to pull back data per the decimal data type. Are you sure it is a double/decimal data type in the database?

Can I ask, If I opened a connection to Access in the module, and used the Sql string to sum up the values where it met a certain criteria defined in each of the 120 functions, would there be any performance improvements? That is instead of calling all the data into an array, then looping through the data with 120 functions?

I reckon that would be a BIG improvement since you won't be having to loop thousands upon thousands of rows.

I am also thinking that instead of having 120 functions (10 measurs for 12 machines), I can have 10 functions and introduce an extra paramter for machine.

This also sounds like a sensible approach to me.