Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    Wichita, Kansas, USA
    Posts
    177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exporting to dbf (Access 2000)

    We have a process we have to go through in which we are pulling info from an Access query (SQL backend) and exporting it into DBF format (because that is the only format the database we are importing into will take). However, there are two fields in the process which change. In Access they look fine, and are fields of Long Integer type.

    After exporting the query to dbf and trying to do the import to the new system, however, it changes two of the fields. Numbers that are supposed to appear as "9742" now appear as "9742.0000" and another field of scores that are usually "79" show up as "79.0000". If I try to open the DBF export file stand-alone (like using Access), it has changed the "types" of these two fields from Long Integer to Double.

    Why does it do this? Have any of you had similar problems? Any ideas?
    Pribb

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Exporting to dbf (Access 2000)

    I believe that you can change the format of the query field. Just position the mouse over the required field in the query in the QBE grid and right click, choose properties and change the format.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    2 Star Lounger
    Join Date
    May 2001
    Location
    Wichita, Kansas, USA
    Posts
    177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting to dbf (Access 2000)

    I guess I'm not making myself very clear :-) The format of the field is fine when in the query window -- it doesn't change until AFTER we do the export. We export into a .DBF file and usually turn right around that import that .DBF file into the new system. (usually without opening in between). Something is happening in the conversion from Access to .dbf.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Exporting to dbf (Access 2000)

    Yes I'm sure it is fine in the query window too. However, have you tried this? AFAIK it should do the trick for you.
    Check <post#=181289>post 181289</post#> for more details.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    2 Star Lounger
    Join Date
    May 2001
    Location
    Wichita, Kansas, USA
    Posts
    177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting to dbf (Access 2000)

    Okay Pat -- I have tried modify the query code to this:

    SELECT TestTab.CourseCode, TestTab.TestName, Int([Personal].[ID]) AS EmpID, Personal.LastName, Personal.FirstName, Int([Answers].[Score]) AS S, Answers.Date, TestTab.MaxCap
    FROM ((Answers INNER JOIN Personal ON Answers.ID = Personal.ID) INNER JOIN Branches ON Personal.BranchLocation = Branches.Num) INNER JOIN TestTab ON Answers.TestNum = TestTab.TestNum
    WHERE (((Int([Answers].[Score]))>=90) AND ((Answers.Date) Between [START DATE] And [END DATE]));

    The bold areas show when I changed to normal query to try to force the format. It still doesn't work.

    Do you see an error, or have more suggestions?
    Pribb

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Exporting to dbf (Access 2000)

    Even formatting the properties of the query prove that it still retains four decimal places for a long integer or integer fields.
    I tried changing the field to a text field and this outputs to the .dbf as a left justified number without the decimal places.
    Have you tried outputting it to a csv file, it outputs as the correct format, ie. without the decimal places.
    What database are you inporting the data into? Can it import a csv file?
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Exporting to dbf (Access 2000)

    Is the problem occurring on the export from access or the import into the other system? Check the data being exported prior to importing the data to the other system to ensure the exported data is in the correct format. It sounds as if the definition of the import parameters may be changing the data type, not the export from access.
    Regards,

    Gary
    (It's been a while!)

  8. #8
    2 Star Lounger
    Join Date
    May 2001
    Location
    Wichita, Kansas, USA
    Posts
    177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exporting to dbf (Access 2000)

    Gary -- your question is the part we can't fully determine (whether the change is happening at time of export or at time of import). When I export to the .dbf, the only way I can check it independently (before import) is by opening the .dbf file in Access. When I do this -- this is where I notice it has changed the type from Long Integer to Double. But whether or not that change is causing the decimal-extended field, I don't know.

    Pat -- in answer to yours -- we did try exporting as a .csv, but couldn't not import into new system that way. We are running a Pathlore system, which has their front end tied to a SQL back end. Their import manager gives us no options other than importing from .dbf I'm actually trying to find a workaround now -- thinking it is stupid to go from SQL backend-to Access frontend - to .dbf export - to .dbf import - to a SQL backend again. Surely there is a better way, but whether or not I can get these other departments to agree to that is another thing. You know how people hate "changing" the status quo.

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Exporting to dbf (Access 2000)

    In Access you can export directly to SQL Server. You can also use an ODBC driver to link to SQL Server tables and simply run append queries to add data to existing tables. Yet another option is DTS if you are running SQL 7 or SQL 2000 - it is the Import/Export function in SQL Server. If you want more details on any of these options, please post back.
    Wendell

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Exporting to dbf (Access 2000)

    Gary, the exported file .dbf contained the decimal point type fields, so that means it is happening from the export from Access.

    I use UltraEdit to look at files if I need to see exactly what is in them.

    Tracey, I wish you had told us earlier about the SQL database at both ends of this problem, because as Wendell says you could have just linked to the tables in both SQL databases and used Append queries to transfer the data.

    Wendell, what is DTS?

    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Exporting to dbf (Access 2000)

    Data Transformation Services. It's a tool for transferring data between databases, including from Access to SQL Server.
    Charlotte

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Exporting to dbf (Access 2000)

    Thanks Charlotte

Posting Permissions

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