VBA Function to retrieve Access data (XP – 2003)

Topic

New Reply

Halloa!

I have a little problem. I’ve a Access Database with tons of data and users who don’t know how to consult it. So, I wrote some easy-to-use Excel’s VBA Functions which are as simple as: a ADODB.Connection and a ADODB.Recordset with a SQL sentence like: SELECT Number FROM Table WHERE Date = ‘v1’ AND Name = ‘v2’ (v1 and v2 are the parameters). When you write =Function(v1,v2) in a cell, it shows up the Number. Easy, isn’t it? Well, my ‘users’ need to retrieve many Numbers. Hence, they use that Function in many cells. I tried multiple solutions, but Excel retrieves the result very slowly (4/5 per second… ). You have to wait for a minute to update 400 cells. Am I clear enough?? Any suggestions?? The database is in a server which is shared with many other people.

I was also wondering: how could I insert ‘help sentences’ in the function wizard of Excel? When you go through Insert -> Formula, a window to chose the formula appears. Once you have selected the function, another window appears where you enter the parameters. I want to describe the formula and the variables for my ‘users’ as it is done in the avaible functions of Excel (Sum(), days360(), day(), month(), etc).

Hope I wrote an undersatable english.

All the best!

Federico.

Reply | Quote

Replies

I don’t work with Access and Excel so I will pass on your first question.

As to your second question, I know of no direct way, but Laurent Longre has a Addin Excel add-ins called “FUNCUSTOMIZE.DLL” which should do what you want.

[Note: You will have to have all your users place this file in the same directory as the workbook!]

Steve

Reply | Quote

Thank you very much Steve. I’ll try it. It seems to be useful.

Have a nice weekend.

Federico.

Reply | Quote

The problem is that each time the function is called, Excel has to create a connection to the database, retrieve a value and close the connection. So if you have 400 cells with the formula, recalculating the sheet opens and closes a connection to the database 400 times.
So we’ll have to find a way to open the connection just once, update all values, then close the connection. The best way to do that depends on the needs of your users. Here are some possibilities:

1) Use a querytable (Data | Get External Data | New Database Query…, can also be done in VBA)
2) Write a macro that uses the CopyFromRecordset method of a range to retrieve data from a query.
3) Write a macro that opens the connection, loops through a range of cells with parameter values, retrieves the corresponding values and writes them to the target cells, then closes the connection.

1) and 2) retrieve an entire query; whether you can use this depends on your users’ needs.
2) and 3) could be activated from a command button.

Reply | Quote

I tried that solutions. They are ingeniuos, but not as adaptable as the single function retrieving a single value.
If I migrated the Access administrated database to a SQL Server administrated database, should I expect it to respond faster (given the same conditions)?

Thank you very much for your answers.

Federico.

Reply | Quote

You might see a slight improvement, but don’t expect wonders. The overhead of opening and closing a connection and a recordset is many times larger than that of retrieving a value from an open recordset – I suspect a factor of 100 or more. So repeating the sequence 400 times will always be *much* slower than .

Reply | Quote

Depending on the layout of the worksheet(s), you might get better results if you could enter the function in multiple cells as one array formula. That way it could open one connection, retrieve the relevant pieces of data and then close the connection again. You would still have the overhead of multiple recordsets, but at least you wouldn’t be opening and closing the connection repeatedly.

Reply | Quote

Federico

If you could link the tables to SQL Server, then you could try the following method. Without knowing your exact setup this may not be entirely appropriate, but you could :

Set up a table in SQL Server which will contain your parameters (v1 and v2, etc) and a blank column for the result.

Create a function/sproc in SQL Server which will populate the blank column with the correct results

Make sure all the 400 or so rows in Excel are correctly populated with the parameters.

Have a command button which will run the code which will :

Copy the Excel sheet into the SQL Server table using ADO

Run the SQL function to poulate the results column.

Copy the fully populated table back into Excel.

This should be fast – you will only need a single connection

HTH

Nick

Reply | Quote

Nick, I don’t have SQL Server avaible yet, but I took notes of your suggestion. I think I’ll choose the array function solution just to resolve the problem to avoid mulplying the time of process by 100 times as HansV said.

Many thanks. Regards,

Federico.

Reply | Quote