Import Access Table contents with criteria.

**JapanDave** · 12-27-2011, 12:17 AM

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

**JapanDave** · 12-27-2011, 06:29 PM

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?

**JohnM3** · 12-27-2011, 09:53 PM

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.

**JapanDave** · 12-27-2011, 11:02 PM

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.

**JohnM3** · 12-27-2011, 11:35 PM

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

**JapanDave** · 12-27-2011, 11:53 PM

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

**JohnM3** · 12-28-2011, 12:49 AM

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

**JohnM3** · 12-28-2011, 01:09 AM

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

**JapanDave** · 12-28-2011, 02:20 AM

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.

**JohnM3** · 12-28-2011, 02:41 AM

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

**JohnM3** · 12-28-2011, 02:54 AM

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

**JapanDave** · 12-28-2011, 09:24 PM

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.

**JohnM3** · 12-29-2011, 11:35 AM

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

**JapanDave** · 12-30-2011, 07:34 PM

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.

**JohnM3** · 12-30-2011, 11:05 PM

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.

Import Access Table contents with criteria.

LinkBack

Thread Tools

Rate This Thread

Display

Import Access Table contents with criteria.

Re: Import Access Table contents with criteria.

Re: Import Access Table contents with criteria.

Re: Import Access Table contents with criteria.

Re: Import Access Table contents with criteria.

Re: Import Access Table contents with criteria.

Re: Import Access Table contents with criteria.

Re: Import Access Table contents with criteria.

Re: Import Access Table contents with criteria.

Re: Import Access Table contents with criteria.

Re: Import Access Table contents with criteria.

Re: Import Access Table contents with criteria.

Re: Import Access Table contents with criteria.

Re: Import Access Table contents with criteria.

Re: Import Access Table contents with criteria.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions