exporting access table data to excel

[cb1]

Are there any common issues/challenges when it comes to exporting a full table of data from an access database out to a currently blank Excel xlsx file? We need to be sure an export of a large table with multiple fields of different data types, exports completely and successfully to a blank excel file - as this data will be used for a data import project into a 3rd party database. I was just trying to establish any common challenges with such an operation to ensure we do not fall foul to common issues.

 

[ebs17]

There are practically never any problems when exporting, but there are certainly problems with subsequent imports.

this data will be used for a data import project into a 3rd party database

In an Excel table, there is no guaranteed data type security for the entire column.

If you have the option, use a text file (CSV). Specifications or Schema.ini are then used here to precisely define how the text file (text table) is to be read. In addition, the text file is slimmer, and every conceivable database should be able to handle text files; this has been a standard since data exchange existed.

 

[cb1]

Thanks for the response ebs17, could you elaborate a little on this please, and how text files help preserve the 'data type security' for all records.
Will suggest using a text file for the export destination and to use this for the import.

In an Excel table, there is no guaranteed data type security for the entire column.

 

[arnelgp]

you need to try/test it first.
you may try:

Docmd.TransferSpreadsheet

command.

 

[ebs17]

Excel doesn't know data types, only formats - VBA in Excel already has data types. A data type can be interpreted from the contents and format of a cell, but each cell can contain something different.
When a read database access is made to an Excel table, the contents of the first rows (the first 8 rows in Access by default) are usually analyzed and the data type for the column is then determined. If a column that was interpreted as a number column then has alphanumeric content in later rows, you quickly have a problem that is more difficult to solve.

A text file contains plain text, so everything is just text. However, an import/export specification determines how these text elements are to be interpreted and read. If these text files are then standardized and generated automatically, you have a very high level of security that they are free of errors.
So I'm focusing on TransferText and related methods.

TransferText Method [Access 2003 VBA Language Reference]

learn.microsoft.com

You can believe me, because only ruins can tell about war.

 

[arnelgp]

double check the output and format it as you wish, it's not a big deal.
that is called proof-reading. there is no place for complacency.

if you need you can do it by VBA:

Sub ExportTableToExcel(ByVal TableName As String, ByVal ExcelFile As String, Optional ByVal SheetName As String = "")
' Note:
' ExcelFile should contain the full path and excel workbookname.xlsx
'

    Dim appExcel As Object
    Dim wb As Object
    Dim ws As Object
    Dim rs As Recordset
    Dim strSQL As String
    Dim i As Integer
    Dim j As Integer
    
    ' Create instance of Excel application
    Set appExcel = CreateObject("Excel.Application")
    'appExcel.Visible = True ' You can set this to False if you don't want Excel to be visible
    
    ' Create a new workbook
    Set wb = appExcel.Workbooks.Add
    
    ' Define your table query
    strSQL = "SELECT * FROM [" & TableName & "];"
    
    ' Open recordset
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot, dbReadOnly)
    
    ' Add a new worksheet
    Set ws = wb.Worksheets.Add
    
    If Len(SheetName) <> 0 Then
        ws.Name = SheetName
    Else
        ws.Name = TableName
    End If
    
    If Not (rs.BOF And rs.EOF) Then
        rs.MoveFirst
    End If
    
    ' Write field names to Excel
    For i = 0 To rs.Fields.Count - 1
        ws.Cells(1, i + 1).Value = rs.Fields(i).Name
    Next i
    
    ' Write recordset data to Excel
    i = 1
    Do While Not rs.EOF
        i = i + 1
        For j = 0 To rs.Fields.Count - 1
            ws.Cells(i, j + 1) = rs.Fields(j)
        Next j
        rs.MoveNext
    Loop
    
    ' Save the workbook as xlsx file
    wb.SaveAs ExcelFile ' Change the path as needed
    
    ' Clean up
    wb.Close
    appExcel.Quit
    Set rs = Nothing
    Set ws = Nothing
    Set wb = Nothing
    Set appExcel = Nothing
    
    MsgBox "Export Complete"
    
End Sub

 

[Pat Hartman]

Another way to check the output with less code is to use TransferText or TransferSpreadsheet to create the export. I also prefer TransferText to create a .csv file but if your exchange partner is dictating a specific format, then that is what you need to provide.

Once you have exported the data, you can link to the spreadsheet and then run a query that compares each field in the two tables to find differences.

 

[ebs17]

Once you have exported the data, you can link to the spreadsheet and then run a query that compares each field in the two tables to find differences.

Has there ever been a documented case of the export containing something other than the source table when using a standard method?

 

[Pat Hartman]

Not that I know of but the OP wanted confirmation. That's an easy way.

 

[CJ_London]

Only thing I would add is that Access does not export any format properties, only the values, so you need to confirm what format is required in excel. Typical examples are a double value in Access of say 1.2345 and formatted to 2dp will appear as 1.23 but will export as 1.2345 or a date field with a time value but formatted to only show the date will export the underlying date and time value.

I would expect your 3rd party to provide a specification stating the order and format of all columns - might be as simple as an example workbook showing some example data.

Personally I would export as a query so you can set the order of columns and use the format and similar functions to present the data in the right order and with the right value formats

 

[raziel3]

I've had a problem with this recently.

Solved - How to Export Current Recordset and remove certain fields to Excel (Field/Column Filtering)

With a form's recordset, How to export the current viewed recordset. I am using this code but it includes all the form's unbounded fields in the export. Private Sub bntEXPORT2EXCEL_Click() Dim Path As String Dim FileName As String FileName = InputBox("Enter File Name to Save As. (Will be...

www.access-programmers.co.uk

Filtering columns was the solution to my problem.