I am in the preliminary stages of trying to figure out a way to get purchase order data out of an accounting program and into an Access database. The Access database has multiple tables that contain all of the necesary information for a purchase (ex. Voucher, Item details, vendors, etc). I am trying to figure out how to structure the output file so that I can get some sort of code to be able to take the file and create records in the appropriate tables. Any suggestions both on how to structure the output file and any code suggestions to accomplish my goal?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Import invoice data into Access
- Thread starter sdannerr
- Start date
- Thread starter
- #3
Unfortuneately, I do not know if the software is ODBC compliant. It is new software that has not been implemented yet so my knowledge of it is limited at this time. I am looking to gain insight ahead of time so that when we implement it I can make sure things are set up appropriately to faciliate exporting info later on. I would assume for the sake of argument that the new system will not be ODBC compliant.
- Thread starter
- #5
First off, if you ask if it is ODBC compliant, your boss will think you are very smart. You should follow this question, if the answer is yes, by demanding that the vendor produce documentation on how they implement ODBC compliance in their system, either through custom drivers or through the database subsystem. You'll be on your way to a raise.
Some vendors build custom drivers, others use database backends like MS SQL Server or Pervasive PSQL which implement ODBC on there own at the database level. Their documentation should include a table layout, and if they are real good, a data dictionary describing the fields in each table. Once that is ascertained, everything else is just programming nitty gritty, such as figuring out what the best data import/export/update model used by Access such as ADO,DAO, or simple macros, will be the most successful approach for whatever the goals are of your application. If it is just updating one set of tables from another ODBC database to another, you can probably do it by linking tables and setting up queries.
Some vendors build custom drivers, others use database backends like MS SQL Server or Pervasive PSQL which implement ODBC on there own at the database level. Their documentation should include a table layout, and if they are real good, a data dictionary describing the fields in each table. Once that is ascertained, everything else is just programming nitty gritty, such as figuring out what the best data import/export/update model used by Access such as ADO,DAO, or simple macros, will be the most successful approach for whatever the goals are of your application. If it is just updating one set of tables from another ODBC database to another, you can probably do it by linking tables and setting up queries.
- Thread starter
- #7
Thanks for the advice. I will definitely keep it in mind as we begin implementing the software. ODBC is definitely the way to go. If a direct connection is not possible, I would still like advice on how to get data from a text file into Access when multiple table need to be updated. Thanks!
- Thread starter
- #9
- Status
Similar threads
- Question
- Locked
- Question
- Question
- Locked
- Question