Results 1 to 12 of 12
  1. #1
    Join Date
    May 2005
    Posts
    28

    Question Unanswered: Opening & Running a VBA in Excel from Access

    So I'm trying to update large data tables in Access, and I keep running out of memory for some reason.

    The Identicle process works in an excel workbook I've created to update the same Access database. Instead of running the code from Excel, I'm hoping there is a way to open the Excel workbook and excute the code therein, so that Excel will be controlling the data, but everything appears seemless through an access form. I've read there is some multi-threaded issue that Access is unable to handle, but Excel is able to handle.

    Anyway, thanks for all your help!

  2. #2
    Join Date
    May 2005
    Posts
    28
    Okay, I've answered this question as well..

    Modules: Run Excel Macros through Automation

    It is possible, and solves my problem.

    ** Spoke too soon. Seems to just delay the inevitable. Apparently Access just gets bored and decides to throw an error if Excel is busy. **

    Anyone know how to make Access pause?

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Running an outside procedure (in excel but run through MSAccess) to update an MSAccess table is not a good approach and will undoubtably give you some kind of error. You can always add an Excel reference if you need to access specialized excel type formula's in vba code which aren't accessible with the normal references. I've rarely had to do this though as typically anything you can do in an excel macro can be easily be done in vba code as it is.

    "I've read there is some multi-threaded issue that Access is unable to handle, but Excel is able to handle." - in 25 years working with MSAccess and Excel, I've never encountered such a problem. I think you may be getting some bad info somewhere or misunderstanding. If anything, excel would be the limiting factor since it is not database driven. If your excel formula does a 'looping' type update, you'll need to research queries or how to write looping type vba code within a recordset.

    I've converted dozens of excel macro coding (sometimes 30 pages long) to vba easily but you do need to know vba fairly well for the complex formulas.

    I wouldn't recommend excel controlling the data for an MSAccess table. Excel serves it's own purpose and should not be intermixed and used for updating into an MSAccess table (especially if it's relational!) That's why you have form designing in MSAccess.
    Last edited by pkstormy; 11-16-09 at 22:08.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    May 2005
    Posts
    28
    I appreciate the perspective you bring pkstormy. Have you ever tried updating an Access table from an asynchronous API? Something like ("just for instance") the BBerg ComV3 API?

    With this it sends requests to the external server and then waits for responses that are returned as data becomes available. Excel handles this perfectly, but Access doesn't seem to want to wait for the return, and consistently gives me an out of memory error after updating between 200-300 data points. Excel can update the same access database with the same programs open on the same system covering some 100,000+ data points, over and over, using almost identicle code.

    Any ideas?

  5. #5
    Join Date
    May 2005
    Posts
    28
    Kudos tp pkstormy. You got me to go back and look harder.. I removed some session calls and viola.. no more memory issues.. I still don't understand my Excel didn't have that problem.. but it works.

    Update: Seems a typo in my code was causing no data to update.. and that's why it was working.. Sigh. Back to the drawing board.
    Last edited by AhrenL; 11-17-09 at 16:35.

  6. #6
    Join Date
    May 2005
    Posts
    28
    Argh.. so it only throws out the memory error when I actually try to change fields in a recordset. I can open them to edit AND update them.. but if I try to change something and then update, after the 5th update it "runs out of memory".

    Any ideas?!

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    AhrenL,

    What you're trying to do is something I haven't done before (I tend to shy away from API calls unless I've gotten them from the Sybex books.) I'm sorry but I don't know how to help you other than typically out of memory issues are usually related to a process looping continuously without a termination point. Although with what you're doing, there could possibly be other reasons for out of memory errors.

    You may want to open up Task Manager and monitor the processes to see if any are eating up a lot of memory while the procedure is run. I often do this with an MSAccess application to see if I have a 'leak' somewhere (ie. perhaps not closing a recordset in code.) Typically what then happens is the MSAccess.exe process grows and grows for memory usage (it should flectuate between 10 meg to 100 meg (large apps.) You'll notice it grow as each procedure is run and it will eventually stabalize (depending on the size of the app but a typical size is between 25meg and 50meg). I'm guessing something like this might be happening either on the MSAccess end or Excel end for you. You can also monitor the Excel.exe process to see if it's perhaps happening on the excel end. I'd look for a large spikage in the memory usage.
    Last edited by pkstormy; 11-17-09 at 22:14.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Is the data in the Excel spreadsheet in several ranges? Or can you import it into Access easily? The process you are using sounds complicated to me. In my experience, when things start getting complicated the programming becomes unreliable (in general).

    I also tend to shy away from Excel because of the assumptions it makes about the data. There are always quirky issues that arise when you have a text field that looks like a number (Zip Codes,etc.)

    You probably don't want to reinvent the wheel, but I would consider getting the data into Access first (Temporary table or link) and then doing any updates from an Access form or module.

  9. #9
    Join Date
    May 2005
    Posts
    28
    Funny, it was because I was relying on the assumptions Excel makes about the data, that my import was working for Excel. The problem was fixed by setting the incoming data to whatever format the field was looking for. Basically the API I was getting data from was sending numbers over in a text format, but only sometimes.

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Excel (and MSAccess) likes to 'self-interpret' what the field type is during importing/exporting based upon what the value is for the first row of data. If it sees a numerical value, it assumes it's a integer type field. For automation, typically an 'Import/Export Specification' is setup to account for these types of mis-interpretation.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I agree that Access does some interpretting also. But, for me, I find it extremely annoying that you cannot override Excel's interpretation. When you import anything other than a spreadsheet, the import wizard guesses at the data type. The user is then allowed to modify the data types if necessary.

    But if you import a spreadsheet with Access, you are not allowed to change data types. You are stuck with the guess that Excel (or Access not sure) has made. The only way I know of to change it, is to first save the spreadsheet as a CSV and then import the CSV. Kind of annoying.

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I'm not up-to-date on my excel coding but isn't there a way to setup an import/export specification in excel? It's been a while but I thought I recalled there was a way to do that. Maybe it's been too long ago.

    Another route you can go instead of creating an excel file, is to first open an excel file and then populate the columns/rows based on the Access table data. I used to do this quite often at an old work-place. I think pootle has some code example to do this posted in the code bank on the 1st or 2nd page if you're interested.

    This works very well for certain scenarios and I thought I'd let you know even though it sounds like it may not be helpful in your situation.

    As for me, I typically have an export routine which works quite nicely in exporting to an excel type format (you can even view in an excel type navigation within MSAcess before actually exporting). The neat thing is there's very little coding involved and it's mostly just manipulating the sourceobject of a subform. If you're interested in seeing it, it's located here: http://www.dbforums.com/6390529-post84.html But try out the export routine (ie. click the export data button while viewing a query) and see if that will be of any help.

    My users tend to like this routine a lot (but I had to remove all the date range criteria boxes and calendar button as that seemed to be confusing.)
    Last edited by pkstormy; 11-22-09 at 04:10.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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