Importing tables into my .mdb


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Importing tables into my .mdb

  1. #1
    Join Date
    Aug 2004
    Posts
    43,023

    Importing tables into my .mdb

    [Originally posted by Chuck Phillips]

    I am currently in the process of automating a data conversion program that is written in access. The Conversion takes dbase IV files from a DOS FoxPro based accounting system to a VB based accounting system using Dbase 5.0 files.

    In my .mdb, I have created all the append queries that I need for the conversion to take place, and they all work fine. I am now putting a frontend on the system that will allow the user to select what module(s) that want to convert (Receivables, Payables, Inventory, etc). The system will automatically pull in all the FoxPro tables, and Dbase 5.0 tables that are needed, run the queries, and export the Dbase 5.0 tables back into the proper system directory.

    Now comes the problem. In all the following code, I have already selected the Paths and Folders. This is the code that I use to import my Dbase 5.0 files, and this works good:

    DoCmd.TransferDatabase acImport, "DBASE 5.0", pstrDestPath, , pstrDestFolder & rstTables!TableName, _
    "CM" & rstTables!TableName, 0

    Here is the bit of code that is causing the problems:

    DoCmd.TransferDatabase acImport, "ODBC Database", _
    "ODBC;DSN=Foxproconversion;UID=User2;PWD=www;LANGUAGE=us_english;" & "DATABASE=pubs" & pstrSourcePath, , rstTables!TableName & pstrSourceComp & strHistory, "AM" & rstTables!TableName & strHistory, 0

    The problem is that when it hits this line to import the FoxPro tables, it wants the user to enter the path to the file. In this conversion 30-40 files will be imported, and I dont want the user to have to select the path each time. I have tried to use the TransferDatabase-Dbase 5.0 method (above) with these files, and that code will import most of the needed foxpro files, but not all. I get a "External Table is Not in Expected Format" error on some of the files.

    Now I could set the path in the bit of ODBC code, but this data exchange will be used in a number of places, and I dont want to have to change the ODBC driver search location each time.

    Question: How do I pass the folder location to the ODBC driver, without it opening up a box each time for user entry?

    Sorry this is so long, but i wanted everyone to know what I was talking about. Thanks for any help.
    -Chuck

  2. #2
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Importing tables into my .mdb

    [Originally posted by Ron Weller]

    Ok I Think I Found The Problem but your code
    is not very clear.
    Your code:
    DoCmd.TransferDatabase acImport, "ODBC Database", _
    "ODBC;DSN=Foxproconversion;UID=User2;PWD=www;LANGUAGE=us_english;" & "DATABASE=pubs" & pstrSourcePath, , rstTables!TableName & pstrSourceComp & strHistory, "AM" & rstTables!TableName & strHistory, 0

    Changes:
    DoCmd.TransferDatabase acImport, "ODBC Database", _
    "ODBC;DSN=Foxproconversion;UID=User2;PWD=www;LANGUAGE=us_english;" & "DATABASE=pubs", , pstrSourcePath & rstTables!TableName & pstrSourceComp & strHistory, "AM" & rstTables!TableName & strHistory, 0

  3. #3
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Importing tables into my .mdb

    [Originally posted by Chuck Phillips]

    here is a 'dictionary' of sorts regarding field names in my code.

    FoxProConversion = the ODBC driver I am wanting to use to import the tables.
    pstrSourcePath = the path to the directory where the data files I am wanting to import are located
    rstTables!TableName = the name of the table I am wanting to import
    pstrSourceComp = the Company identification number of the files
    strHistory = determines whether the file is a history file or a current file

    for example:
    pstrSourcePath = F:\amp\
    rstTables!TableName = artrs
    pstrSourceComp = 01
    strHistory = H

    this would import the above file into the database.
    The reason for the SourceComp is that a user could run several different companies out of the same accounting software, and strHistory tell whether the file is a history file ('H') or a current file ('').
    I hope that clarifies things.

    by the way, I have tried your change and it still prompts the user for the table path each time. Is it possible I havent set up my ODBC driver correctly?

  4. #4
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Importing tables into my .mdb

    [Originally posted by Ron Weller]

    Ok I here is the problem. Acces does not know
    where thr foxpro database is. If "pubs" is the
    database name then the path would come before the
    database name not after it. If "pubs" is part of
    the path then you need to start from somewhere.

    Pubs is part of path so start it from "C:\":
    DoCmd.TransferDatabase acImport, "ODBC Database", _
    "ODBC;DSN=Foxproconversion;UID=User2;PWD=www;LANGUAGE=us_english;DATABASE=C:\pubs" & pstrSourcePath, , rstTables!TableName & pstrSourceComp & strHistory, "AM" & rstTables!TableName & strHistory, 0


    Pubs is the database name:
    DoCmd.TransferDatabase acImport, "ODBC Database", _
    "ODBC;DSN=Foxproconversion;UID=User2;PWD=www;LANGUAGE=us_english;DATABASE=" & pstrSourcePath & "pubs", , rstTables!TableName & pstrSourceComp & strHistory, "AM" & rstTables!TableName & strHistory, 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
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center