VBA Export Access Table as dBase File

  • (2 Pages)
  • +
  • 1
  • 2

20 Replies - 3302 Views - Last Post: 10 June 2013 - 08:36 PM Rate Topic: -----

#1 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 221
  • View blog
  • Posts: 1,030
  • Joined: 25-June 12

VBA Export Access Table as dBase File

Posted 06 June 2013 - 11:22 AM

I have the below Export procedure coded to transfer data from my Excel "Application" into an Access data table. I then need to Export the Access Table into a dBase file through VBA.

Can anyone assist me with this?

Sub Export()
    Dim dbConnection As ADODB.Connection
    Dim dbFileName As String
    Dim dbRecordset As ADODB.Recordset
    Dim xRow As Long, xColumn As Long
    Dim LastRow As Long
    
    'Go to the worksheet containing the records you want to transfer.
    Worksheets("FeedSamples").Activate
    'Determine the last row of data based on column A.
    LastRow = Cells(Rows.Count, 1).End(xlUp).row
    'Create the connection to the database.
    Set dbConnection = New ADODB.Connection
    'Define the database file name
    dbFileName = "filePath\FeedSampleResults.accdb"
    'Define the Provider and open the connection.
    With dbConnection
        .Provider = "Microsoft.ACE.OLEDB.12.0;Data Source=" & dbFileName & _
        ";Persist Security Info=False;"
        .Open dbFileName
    End With
    'Create the recordset
    Set dbRecordset = New ADODB.Recordset
    dbRecordset.CursorLocation = adUseServer
    dbRecordset.Open Source:="ImportedData", _
    ActiveConnection:=dbConnection, _
    CursorType:=adOpenDynamic, _
    LockType:=adLockOptimistic, _
    Options:=adCmdTable
    'Loop thru rows & columns to load records from Excel to Access.
    'Assume row 1 is the header row, so start at row 2.
    'ACCESS COLUMNS MUST BE NAMED EXACTLY THE SAME AS EXCEL COLUMNS
    For xRow = 2 To LastRow
        dbRecordset.AddNew
        'Assume this is an 8-column (field) table starting with column A.
        For xColumn = 1 To 69
            dbRecordset(Cells(1, xColumn).value) = Cells(xRow, xColumn).value
        Next xColumn
        dbRecordset.Update
    Next xRow
    
    'Close the connections.
    dbRecordset.Close
    dbConnection.Close
    'Release Object variable memory.
    Set dbRecordset = Nothing
    Set dbConnection = Nothing
    'Optional:
    'Clear the range of data (the records) you just transferred.
    'Range("A2:H" & LastRow).ClearContents
    MsgBox "Test"
    Dim access As access.Application
    Set access = "filePath\FeedSampleResults.accdb"
    access.DoCmd.OpenTable "ImportedData"
    access.DoCmd.TransferDatabase acExport, "dBASE IV", "C:\", acTable, "ImportedData", "TEST.DBF"
    DoCmd.Close acTable, "ImportedData"
    
End Sub

This post has been edited by AnalyticLunatic: 06 June 2013 - 12:02 PM


Is This A Good Question/Topic? 0
  • +

Replies To: VBA Export Access Table as dBase File

#2 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3175
  • View blog
  • Posts: 10,625
  • Joined: 12-December 12

Re: VBA Export Access Table as dBase File

Posted 06 June 2013 - 11:52 AM

I see that you made an attempt (erm, wild guesses :whistling:) towards the end of your code.

Please don't name the Access instance access. Notice how access.Application has now assumed lowercase a.

    Dim acx As Access.Application    'or myAccess (or anything other than 'access')
    Set acx = New Access.Application
    acx.OpenCurrentDatabase ("\\wibbly\woo\whehay.accdb")

I haven't tested this - I'll leave that to you.

This post has been edited by andrewsw: 06 June 2013 - 12:03 PM

Was This Post Helpful? 1
  • +
  • -

#3 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3175
  • View blog
  • Posts: 10,625
  • Joined: 12-December 12

Re: VBA Export Access Table as dBase File

Posted 06 June 2013 - 12:06 PM

You don't need to open and close the imported table.
Was This Post Helpful? 1
  • +
  • -

#4 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 221
  • View blog
  • Posts: 1,030
  • Joined: 25-June 12

Re: VBA Export Access Table as dBase File

Posted 06 June 2013 - 12:11 PM

I've got the below now. I am receiving "Run-time error '3016' Field will not fit in record.". Some people from 2004 suggested ensuring the field types were Text with field sizes of 255 (they all are besides the ID field which is an autonumber).

Any ideas? Zero experience with .DBF Files.

    Dim acx As access.Application
    Set acx = New access.Application
    acx.OpenCurrentDatabase ("filePath\FeedSampleResults.accdb")
    acx.DoCmd.TransferDatabase acExport, "dBase IV", "filePath\Personal Project Notes\", acTable, "ImportedData", "TEST.DBF"
    acx.DoCmd.Close acTable, "ImportedData"
    acx.CloseCurrentDatabase

This post has been edited by AnalyticLunatic: 06 June 2013 - 12:11 PM

Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3175
  • View blog
  • Posts: 10,625
  • Joined: 12-December 12

Re: VBA Export Access Table as dBase File

Posted 06 June 2013 - 12:34 PM

Please tell us the line that the error occurs on. (Haven't I mentioned this before..?)

  • I would double check all of the fields, their order, data-types, the specific values you are trying to export. I would confirm what should happen in place of the autonumber field. Should it be skipped entirely, or maybe it needs to be supplied as a column of null values.
  • I would consider creating an Access Macro to transfer the data and running this to see if it, firstly, works, or if not, gives a different error message.
  • Double check the TransferDatabase arguments.
  • Remove the line to close the table - it is not necessary.
  • As you are running this from Excel, temporarily replace the intrinsic (Access) constants with their numeric equivalents: acExport is 1, acTable is 0. (This shouldn't be necessary, but I would check this anyway.)


Exercise your debugging and trouble-shooting brain :hammer: [The smiley intends "hammer away at the problem"]

This post has been edited by andrewsw: 06 June 2013 - 12:38 PM

Was This Post Helpful? 0
  • +
  • -

#6 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 221
  • View blog
  • Posts: 1,030
  • Joined: 25-June 12

Re: VBA Export Access Table as dBase File

Posted 06 June 2013 - 01:34 PM

Sorry, for some reason I thought it clearly as the TransferDatabase line, but now I see it would have been better to state that explicitly. All fields between Excel & Access are in the same format, spelling, and order. Taking out the auto-number field still produces the same error. All arguments appear correct for the method, though I am questioning does the .DBF already need to exist, or, will this code create the file (my original line of thinking)?

Line to close the table deleted.

:hammer: :hammer: :hammer: :hammer: :hammer:

Sub Export()
    Dim dbConnection As ADODB.Connection
    Dim dbFileName As String
    Dim dbRecordset As ADODB.Recordset
    Dim xRow As Long, xColumn As Long
    Dim LastRow As Long
    
    'Go to the worksheet containing the records you want to transfer.
    Worksheets("FeedSamples").Activate
    'Determine the last row of data based on column A.
    LastRow = Cells(Rows.Count, 1).End(xlUp).row
    'Create the connection to the database.
    Set dbConnection = New ADODB.Connection
    'Define the database file name
    dbFileName = "filePath\Personal Project Notes\FeedSampleResults.accdb"
    'Define the Provider and open the connection.
    With dbConnection
        .Provider = "Microsoft.ACE.OLEDB.12.0;Data Source=" & dbFileName & _
        ";Persist Security Info=False;"
        .Open dbFileName
    End With
    'Create the recordset
    Set dbRecordset = New ADODB.Recordset
    dbRecordset.CursorLocation = adUseServer
    dbRecordset.Open Source:="ImportedData", _
    ActiveConnection:=dbConnection, _
    CursorType:=adOpenDynamic, _
    LockType:=adLockOptimistic, _
    Options:=adCmdTable
    'Loop thru rows & columns to load records from Excel to Access.
    'Assume row 1 is the header row, so start at row 2.
    'ACCESS COLUMNS MUST BE NAMED EXACTLY THE SAME AS EXCEL COLUMNS
    For xRow = 2 To LastRow
        dbRecordset.AddNew
        'Assume this is an 8-column (field) table starting with column A.
        For xColumn = 1 To 69
            dbRecordset(Cells(1, xColumn).value) = Cells(xRow, xColumn).value
        Next xColumn
        dbRecordset.Update
    Next xRow
    
    'Close the connections.
    dbRecordset.Close
    dbConnection.Close
    'Release Object variable memory.
    Set dbRecordset = Nothing
    Set dbConnection = Nothing
    'Optional:
    'Clear the range of data (the records) you just transferred.
    'Range("A2:H" & LastRow).ClearContents
    MsgBox "Test"
    
    Dim acx As access.Application
    Set acx = New access.Application
    acx.OpenCurrentDatabase ("filePath\Personal Project Notes\FeedSampleResults.accdb")
    acx.DoCmd.TransferDatabase acExport, "dBase IV", "filePath\Personal Project Notes\", acTable, "ImportedData", "TEST.DBF"
    acx.CloseCurrentDatabase
    
End Sub

This post has been edited by AnalyticLunatic: 06 June 2013 - 01:35 PM

Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3175
  • View blog
  • Posts: 10,625
  • Joined: 12-December 12

Re: VBA Export Access Table as dBase File

Posted 06 June 2013 - 01:43 PM

I believe this "filePath\Personal Project Notes\" should be pointing at an existing database file. I've never touched dBase files but I doubt that TransferDatabase will build a database for you.

Added: I dunno though. Is a dBase database a collection of .dbf files/tables?

This post has been edited by andrewsw: 06 June 2013 - 01:48 PM

Was This Post Helpful? 0
  • +
  • -

#8 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 221
  • View blog
  • Posts: 1,030
  • Joined: 25-June 12

Re: VBA Export Access Table as dBase File

Posted 06 June 2013 - 01:51 PM

View Postandrewsw, on 06 June 2013 - 08:43 PM, said:

I believe this "filePath\Personal Project Notes\" should be pointing at an existing database file. I've never touched dBase files but I doubt that TransferDatabase will build a database for you.

Added: I dunno though. Is a dBase database a collection of .dbf files/tables?


I honestly have not a clue. I've never worked with dBase nor .DBF Files.
Was This Post Helpful? 0
  • +
  • -

#9 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3175
  • View blog
  • Posts: 10,625
  • Joined: 12-December 12

Re: VBA Export Access Table as dBase File

Posted 06 June 2013 - 01:57 PM

Quote

All fields between Excel & Access are in the same format, spelling, and order.

Yes but the data-types of fields will be different between Access and dBase. An integer in Access may be different to an integer in dBase (different size). I would check.

For example, dBase files don't have long integers; they will be converted to double.

http://support.microsoft.com/kb/891775

This is not an issue which would prevent the export, but there may be issues with other data-types.
Was This Post Helpful? 0
  • +
  • -

#10 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3175
  • View blog
  • Posts: 10,625
  • Joined: 12-December 12

Re: VBA Export Access Table as dBase File

Posted 06 June 2013 - 02:13 PM

Hey, although the Excel option to save as "dbf" was removed in Excel 2007, I reckon it might still work in code. Something like this:

Sub FromExcelToDbf()
    Dim wb As Workbook
    Workbooks(2).SaveAs "test.csv", xlCSV 'or another text file-format enumeration
    Workbooks(2).Close
    Set wb = Workbooks.Open("test.csv", , , 2)  '2 states "commas" as delimiters
    wb.SaveAs "test.dbf", xlDBF4
End Sub


Added: Mmm.. maybe not :dontgetit:

This post has been edited by andrewsw: 06 June 2013 - 02:16 PM
Reason for edit:: .csv

Was This Post Helpful? 0
  • +
  • -

#11 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 221
  • View blog
  • Posts: 1,030
  • Joined: 25-June 12

Re: VBA Export Access Table as dBase File

Posted 07 June 2013 - 06:16 AM

View Postandrewsw, on 06 June 2013 - 08:57 PM, said:

Quote

All fields between Excel & Access are in the same format, spelling, and order.

Yes but the data-types of fields will be different between Access and dBase. An integer in Access may be different to an integer in dBase (different size). I would check.

For example, dBase files don't have long integers; they will be converted to double.

http://support.microsoft.com/kb/891775

This is not an issue which would prevent the export, but there may be issues with other data-types.


I understand what your saying. Starting to look like I need to create the dBase file before I'm able to export to it... :whatsthat:

After reviewing this thread (be it from 2005) it looks like you can set field lengths in dBase files.

View Postandrewsw, on 06 June 2013 - 09:13 PM, said:

Hey, although the Excel option to save as "dbf" was removed in Excel 2007, I reckon it might still work in code. Something like this:

Sub FromExcelToDbf()
    Dim wb As Workbook
    Workbooks(2).SaveAs "test.csv", xlCSV 'or another text file-format enumeration
    Workbooks(2).Close
    Set wb = Workbooks.Open("test.csv", , , 2)  '2 states "commas" as delimiters
    wb.SaveAs "test.dbf", xlDBF4
End Sub


Added: Mmm.. maybe not :dontgetit:/>


Thanks for the attempt ^^ I did a fair amount of research and for my particular issue it looks like I have 3 options:

  • Pay for the Excel Plug-in created by Gulyas: http://thexlwiz.blogspot.co.uk/
  • Export to Access & then export to dBase.
  • Create a custom method somehow (out of my skill-level at this point).

Was This Post Helpful? 0
  • +
  • -

#12 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3175
  • View blog
  • Posts: 10,625
  • Joined: 12-December 12

Re: VBA Export Access Table as dBase File

Posted 07 June 2013 - 06:48 AM

These updates are not in real-time. So you could export the data to Access or csv and, manually, once a day/week someone could export/import the data to dBase, hopefully just pushing a button. This would give you time to work on the bridging solution.

But my suspicion is that you are not far away from a solution. You just need to solve the issues you are having with TransferDatabase (to dBase). I may be wrong though!

Good luck anyways.

[If only a few people need access to the data in dBase then create a .csv file and let them import it themselves :bigsmile: ]

Quote

After reviewing this thread (be it from 2005) it looks like you can set field lengths in dBase files.

Rather than increasing the field lengths in dBase you could reduce them in Access, checking first that you won't lose any information.
Was This Post Helpful? 0
  • +
  • -

#13 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3175
  • View blog
  • Posts: 10,625
  • Joined: 12-December 12

Re: VBA Export Access Table as dBase File

Posted 07 June 2013 - 06:53 AM

Did you read the entire page that you linked to?

Quote

Also, dBase has a 10 character field name limit and does not like spaces or special characters.

Personally, I'd create the table in dBase, link it, and export the data with
an append query.

Was This Post Helpful? 0
  • +
  • -

#14 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 221
  • View blog
  • Posts: 1,030
  • Joined: 25-June 12

Re: VBA Export Access Table as dBase File

Posted 07 June 2013 - 06:53 AM

View Postandrewsw, on 07 June 2013 - 01:48 PM, said:

These updates are not in real-time. So you could export the data to Access or csv and, manually, once a day/week someone could export/import the data to dBase, hopefully just pushing a button. This would give you time to work on the bridging solution.


Fair idea. Just for kicks I just tried manually selecting the table in access and performing the gui-route of export to dBase, but (thankfully?) received the same error about field not fitting in record.

View Postandrewsw, on 07 June 2013 - 01:48 PM, said:

But my suspicion is that you are not far away from a solution. You just need to solve the issues you are having with TransferDatabase (to dBase). I may be wrong though!

Good luck anyways.


Agreed. I'll keep :hammer: and see what I come up with. Thanks! ^^

View Postandrewsw, on 07 June 2013 - 01:48 PM, said:

[If only a few people need access to the data in dBase then create a .csv file and let them import it themselves :bigsmile:/> ]

Quote

After reviewing this thread (be it from 2005) it looks like you can set field lengths in dBase files.

Rather than increasing the field lengths in dBase you could reduce them in Access, checking first that you won't lose any information.


I'll give this a shot and get back to you on results!
Was This Post Helpful? 0
  • +
  • -

#15 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3175
  • View blog
  • Posts: 10,625
  • Joined: 12-December 12

Re: VBA Export Access Table as dBase File

Posted 07 June 2013 - 07:07 AM

Quote

Fair idea. Just for kicks I just tried manually selecting the table in access and performing the gui-route of export to dBase, but (thankfully?) received the same error about field not fitting in record.

You could perhaps concentrate on getting the gui-method (or macro) to work - it is quicker and easier to test than the coded version. Once you've achieved this then you'll know that the coded version can be made to work, and you will be more aware of the issues involved.

This post has been edited by andrewsw: 07 June 2013 - 07:09 AM

Was This Post Helpful? 1
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2