Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Location
    Castelar
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Function to retrieve Access data (XP - 2003)

    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.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VBA Function to retrieve Access data (XP - 2003)

    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 <img src=/S/free.gif border=0 alt=free width=30 height=15>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

  3. #3
    New Lounger
    Join Date
    Feb 2004
    Location
    Castelar
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Function to retrieve Access data (XP - 2003)

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

    Have a nice weekend.

    Federico.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Function to retrieve Access data (XP - 2003)

    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.

  5. #5
    New Lounger
    Join Date
    Feb 2004
    Location
    Castelar
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Function to retrieve Access data (XP - 2003)

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Function to retrieve Access data (XP - 2003)

    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 <open a connection, open a recordset, retrieve a value, close the record set and close the connection> 400 times will always be *much* slower than <open a connection, open a recordset, retrieve 400 values, close the recordset, close the connection>.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VBA Function to retrieve Access data (XP - 2003)

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Function to retrieve Access data (XP - 200

    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

  9. #9
    New Lounger
    Join Date
    Feb 2004
    Location
    Castelar
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Function to retrieve Access data (XP - 200

    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.

Posting Permissions

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