+ Reply to Thread
Results 1 to 15 of 15

Import Access Table contents with criteria.

  1. #1
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Import Access Table contents with criteria.

    I have been for weeks trying to get some code to import data from an Access Table between two specified dates into an excel file. I am trying to set an invoicing system that use Excel on the front end and Access on the back end to store the data.

    The table is simple enough with 5 columns, column 1 being the date the item was sold and the other 4 column showing the description of the product, how many items sold, the unit price and finally the total of the unit price and number sold.

    I am trying to import data at the the end of every month, which the criteria is always the previous two month data starting at the 16th of the month in question and ending two month later on the 15th.

    Eg. Today is Decmber 27th, so I need the data from October the 16th to December the 15th. Exactly 2 months worth of data. Next month will be November 16th to January 16th. etc, etc.

    I preferably want to do this via excel if I can.
    I have tried unsuccessfully to manipulate this code I got from the web.

    Please Login or Register  to view this content.
    Cheers

    Dave
    Be fore warned, I regularly post drunk. So don't take offence (too much) to what I say.
    I am the real 'Napster'
    The Grid. A digital frontier. I tried to picture clusters of information as they moved through the computer. What did they look like? Ships? motorcycles? Were the circuits like freeways? I kept dreaming of a world I thought I'd never see. And then, one day...

    If you receive help please give thanks. Click the * in the bottom left hand corner.

    snb's VBA Help Files

  2. #2
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Import Access Table contents with criteria.

    I thought it would be a long shot. So can someone just point me to some code that will import one column no criteria and I can figure out the rest?

  3. #3
    Forum Contributor
    Join Date
    12-26-2010
    Location
    Kansas City, Kansas
    MS-Off Ver
    Excel 2007
    Posts
    147

    Re: Import Access Table contents with criteria.

    Hi J

    The code below seems to work and was tested. You will have to change several
    items as indicated in the code.

    hth
    regards
    John

    Please Login or Register  to view this content.

  4. #4
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Import Access Table contents with criteria.

    Hey John,

    Thanks for taking the time to reply. I do however keep getting an error saying it can't recognize the file. The error shows the DB path , but the path is correct, the file name also matches. So I am pretty much stumped right now.

  5. #5
    Forum Contributor
    Join Date
    12-26-2010
    Location
    Kansas City, Kansas
    MS-Off Ver
    Excel 2007
    Posts
    147

    Re: Import Access Table contents with criteria.

    Hi J

    >>
    I do however keep getting an error saying it can't recognize the file.
    >>

    I put file checking in the code to test whether the files exists -
    does the error message say - in part - "File does not exist ..."

    This is the code that tests for the files existence - its part of the code I posted ..

    Please Login or Register  to view this content.
    If that is the code that is firing off - then without a doubt - the directory + Filename
    does not exist.

    Can you post back precisely the directory and file name shown in the error message?

    regards
    John

  6. #6
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Import Access Table contents with criteria.

    I get the error when it gets to the .open line in the below code.

    Please Login or Register  to view this content.
    It jumps to the error handler.

    The message I get is , (Please remember I am translating this from Japanese, so it won't be the exact wording on an English ver of excel)
    Please Login or Register  to view this content.

    Cheers

    Dave

  7. #7
    Forum Contributor
    Join Date
    12-26-2010
    Location
    Kansas City, Kansas
    MS-Off Ver
    Excel 2007
    Posts
    147

    Re: Import Access Table contents with criteria.

    Hi J

    Thank you very much for posting that.

    (a) We are past the Does Files exist thingie.

    (b) Either - it is burping on sProvider or sDataSource

    Can you put an F9 on the line .OPEN

    Then can your put sProvider and sDataSource in the debugger and confirm
    that they are as expected.

    As I posted - the code I posted works - but you have disproved this given your
    outcome.

    So - I want to propose TWO changes.

    First - can you confirm that you can manually open up the ACCESS database.
    Second - I want to modify the sDataSource Variable as follows:

    '------------------------------
    '------------------------------
    'Current
    sDataSource = "Data Source = " & sPath & sACCDBName

    'Add
    sDataSource = "Data Source = " & "'" & sPath & sACCDBName & "'"
    sDataSource = sDataSource & ";"

    I have tested the revision and it also works.

    regards
    John

  8. #8
    Forum Contributor
    Join Date
    12-26-2010
    Location
    Kansas City, Kansas
    MS-Off Ver
    Excel 2007
    Posts
    147

    Re: Import Access Table contents with criteria.

    Hi J

    I found the ADO error using google and it is here:
    http://support.microsoft.com/kb/209050

    -2147467259 (0x80004005) Unspecified Error

    This kind of error leaves us no place to go so I want to make a suggestion.

    (a) Manually create a NEW BLANK ACCESS database. You do NOT have to add any tables.

    (b) Modifiy the code I post to point to the new BLANK database.

    (c) Using F8 - walk down the code and see if it still crashes on the .OPEN line.

    regards
    John

  9. #9
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Import Access Table contents with criteria.

    Hey John, thanks so much for your help so far.

    Unfortunately , even with those changes made in your last two post I am getting this same error. I have a sneaky feeling that the Japanese OS windows 7 may have something to do with this.

  10. #10
    Forum Contributor
    Join Date
    12-26-2010
    Location
    Kansas City, Kansas
    MS-Off Ver
    Excel 2007
    Posts
    147

    Re: Import Access Table contents with criteria.

    Hi J

    Please see my previous post about creating a NEW BLANK database.

    If that fails - I am not certain whatelse to do.

    regards
    John

  11. #11
    Forum Contributor
    Join Date
    12-26-2010
    Location
    Kansas City, Kansas
    MS-Off Ver
    Excel 2007
    Posts
    147

    Re: Import Access Table contents with criteria.

    Hi J

    I bumped into these two thingies - BUT - I _ain't_ literate enough to read them.

    But please note:

    This is not an issue in Visual Studio .NET if you are using ADO.NET. But if you are using ADO, it is an issue. For .NET specific issues, see KnowledgeBase article 840667: You receive unexpected errors when using ADO and ADO Multidimensional in a .NET Framework application

    http://www.fmsinc.com/blog/post/Micr...ce-Pack-1.aspx

    http://support.microsoft.com/kb/2517589

    Perhaps one of the forum moderators have bumped up against this and so you might
    want to email one of them.

    Very sorry I could not help you.

    regards
    John

  12. #12
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Import Access Table contents with criteria.

    Unfortunately, even after trying the blank DB I am still getting the same error. I am going to try a few different things and I will get back to this thread to report how I go. Thanks so much for help John.

  13. #13
    Forum Contributor
    Join Date
    12-26-2010
    Location
    Kansas City, Kansas
    MS-Off Ver
    Excel 2007
    Posts
    147

    Re: Import Access Table contents with criteria.

    Hi J

    Please read the links on my last post. There maybe a service pack issue with OS7 that kills ***ADO***.

    I am not certain if it applies to your situation. I am _not_ certain if I am reading the link correctly,
    but I think so.

    regards
    John

  14. #14
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Import Access Table contents with criteria.

    Hi John,

    OK, went through a whole heap of literature. It seems that the problem you speak of in your link is not what is happening to me. I can't understand it as I have another program using Access on the back end of excel. Of corse I did not do the code, but realized how useful using access on the back end of Excel can be, which is why I wanted to be able to make some code up myself. I am going to leave the thread unsolved for now and hopefully I will be able to come up with a solution and post it up.

  15. #15
    Forum Contributor
    Join Date
    12-26-2010
    Location
    Kansas City, Kansas
    MS-Off Ver
    Excel 2007
    Posts
    147

    Re: Import Access Table contents with criteria.

    Hi J

    This is just as any FYI. Code below tested and works. Uses DAO in place of ADO.

    If u decide to give it a shot - DO NOT - reference DAO 3.6 in the references thingie - instead -
    reference

    ' * Tools -> References -> Microsoft Office 12.0 Access Database engine Object Library

    You can leave the current ADO references "as is".

    regards
    John


    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1