ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Crystal Reports, ODBC & Access >> Access General Issues >> Exporting tables into Access
http://www.istechforum.com/YaBB.pl?num=1093984939

Message started by davson on 08/31/04 at 12:42:18

Title: Exporting tables into Access
Post by davson on 08/31/04 at 12:42:18

Hi everyone, I'm new to this so bare with me.

We are thinking about exporting DBA tables into Access in order to generate more useful reports. I have never done this before and I was after some helpful tips and advice.

Title: Re: Exporting tables into Access
Post by Skeptical on 09/01/04 at 05:24:29

Unless there is a particular need to actually transfer the data to Access, I would recommend linking Access to DBA instead. Maintaining two copies of your data can be a pain and often requires nightly imports which can leave your reporting data as much as 24hrs out of date with the live DBA data.

Use an ODBC DSN to link the DBA tables into Access instead of actually transferring them over. As far as reporting, querying and coding Access, this method functions almost identical to having the tables imported.

Title: Re: Exporting tables into Access
Post by davson on 09/01/04 at 06:17:26

Thanks for your help 'Skeptical'. That idea sounds much better than want I was going to do. Do you know how I go about getting the ODBC DSN setup? Would I set that up from within Access? I also have no idea how I link the tables together with DBA. Is there an option meni within DBA to do this? Sorry for all this questions I am very new at this. Thanks again. ;D

Title: Re: Exporting tables into Access
Post by Kelloggs on 09/01/04 at 11:55:11

Check this website

http://www.mwaccounting.com/dba/white_paper.htm

it's very easy to link pervasive to ms access

Regards,

Kelloggs

Title: Re: Exporting tables into Access
Post by davson on 09/01/04 at 12:09:46

Thanks for the link to the web site. This is probably a stupid question but do I have to use Pervasive to be able to use Access?

Title: Re: Exporting tables into Access
Post by Kelloggs on 09/01/04 at 12:14:12

What you MUST use to connect MS Access is Pervasive ODBC engine, which you have already installed on your computer.

ciao,

Kelloggs

Title: Re: Exporting tables into Access
Post by davson on 09/01/04 at 12:19:27

OK thanks that makes sense.

Title: Re: Exporting tables into Access
Post by Skeptical on 09/07/04 at 05:06:49

So, how's it going Davson?
You all hooked up?

Title: Re: Exporting tables into Access
Post by davson on 09/07/04 at 05:51:13

Yea I am at long last. Thanks again for your help, it all seem so simple now I've actually done it. lol.

I do have some other issues that you may be able to help me with:

Background: I am trying to use MS Access to generate a report which is similar to Invoice IBKSOF4.RTM with a few extra fields and a different design.

1. I am having difficulty finding the table which contains the 2nd line description for an Item which is entered in DBA on IN-B. I can find the 1st line of the description in BKARINVL table and most of the other information on the DBA report but not the 2nd line description. This must be stored on a different table does anyone have any ideas where?

2. In DBA the IBKSOF4.RTM invoice report shows serial numbers related to each Item which I am trying to display in my Access report, but I can't find the Tables which hold ALL the serial numbers. The DBA SERIAL table holds some serial numbers but not all of them that is displayed on the DBA invoice report. I have looked in the MASTER Serial table which I hoped would display all serial numbers but it doesn't. Does anyone have any ideas which tables I can look in?

3. Any finally in table SERIAL there are multiple serial numbers for the same item and these duplicates are not displayed on IBKSOF4.RTM. In Access does anyone know how can I display only each unique serial number and not all the other duplicates?

Well I don't know if anyone can help but I would appreciate your comments. Thanks.

Title: Re: Exporting tables into Access
Post by Lynn Pantic on 09/07/04 at 05:55:37

Are you trying to print unposted invoices or reprint posetd invoices after the fact? Printing unposted invoices won't replace using DBA SO-F because it needs to write back to the file and flag the Sales Order as printed & ready to post. Also, the tables you need to look at depend whether the invoice is posted yet or not.

Title: Re: Exporting tables into Access
Post by davson on 09/07/04 at 06:09:29

Thanks.
I always reprint previously posted invoices. Can you help with my issues?

Title: Re: Exporting tables into Access
Post by Lynn Pantic on 09/07/04 at 06:34:47

I was hoping you would say that, it makes things a bit easier.

The inventory second description line is in BKICMSTR, field BKIC.PROD.NOTE but it is typically pulled into the sales order as a comment line so it would also be in BKARHIVL as BKAR.INV.DESC for the line immediately after the item line on the invoice.

Also, if you are reprinting posted invoices you should NOT be using BKARINVL but instead BKARHINV header and BKARHIVL line item files. Associated serial numbers are stored in BKSOHSER tied to the invoice number.

Title: Re: Exporting tables into Access
Post by davson on 09/07/04 at 06:53:36

Wow thats great and such a quick response - Thanks!
I will check out the description info. shortly. Yesterday I actually tried to use BKSOHSER table and I found two problems:

1. Not all the serial numbers on IBKSOF4.RTM invoice report in DBA appeared in the BKSOHSER table. Is it possible there is another table that is been used to store the other serial numbers?

2. There were many duplicate records for each item in BKSOHSER table. Is this a problem with the way we have setup our system and if so then do you know how we can rectify the problem? If not does it normally have duplicates and why?

Thanks again for your help Lynn.

Title: Re: Exporting tables into Access
Post by davson on 09/07/04 at 07:13:55

Ok so I looked for the 2nd line of the item description in BKICMSTR table and there was no information in there so I assumed as you said that it was pulled into BKARHIVL as BKAR.INVL.DESC but this only showed the 1st line of the description. Is there another table I can use? Thanks.

Title: Re: Exporting tables into Access
Post by Lynn Pantic on 09/07/04 at 07:58:18

You should be looking at the BKSOHSER by invoice number so you should only get the serial numbers associated with a given invoice.

If an item has a second description line in IN-A then the BKIC.PROD.NOTE in BKICMSTR will have that information. Linking to the BKARHIVL table will be trickier because it is not the same record as the line item - it is the NEXT record in the file.

Title: Re: Exporting tables into Access
Post by davson on 09/07/04 at 09:47:32

Hi Lynn, well I found out what the problem was. I was getting crappy information appearing in my linked tables for some reason. When I do a import of a table (eg BKSOHSER) then I get no duplicates and the correct information appears that corresponds with Maintain Database information, but when I do Link Tables then I get duplicates.
Thanks for your patience.
Do you have any idea why Access import displays the correct information but the link tables does not?

Title: Re: Exporting tables into Access
Post by aricon on 09/07/04 at 09:56:10

There should be no reason for any difference. When you are linking, what are you choosing as the primary key field for Access?

Title: Re: Exporting tables into Access
Post by davson on 09/07/04 at 10:05:35

Thanks Lorne. I see what I was doing wrong I wasn't choosing a unique field for my primary key on the link.
How dumb do I have to be! Thanks all problems now solved.

Title: Re: Exporting tables into Access
Post by aricon on 09/07/04 at 10:15:15