Link to home
Start Free TrialLog in
Avatar of hindersaliva
hindersalivaFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Export Access table as CSV (from Excel)

I need to fire off a command from Excel 2016 to export tables (as CSV) from an Access 2016 database.

The samples I have seen are DAO. (DoCmd)
I already have an ADO connection in my Excel app. Can I use that?

Sample code would help. Thanks.

Steps:
List the table names on a sheet
Loop through. Export each table to a CSV file with filename as table name
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

do you have any reason why you want to do this in Excel and not in Access?
Avatar of hindersaliva

ASKER

Rae, I've developed a teamwork Excel application that stores its data in Access. I'd like keep all the functionality within the Excel UI. So far there is no reason for the users to go anywhere near the Access database. I'd like to keep the database 'hands off'.
<I already have an ADO connection in my Excel app. Can I use that? > Yes
can you post the codes?
Public objConn As New ADODB.Connection

Sub DBConnectionAccess()

    If CBool(objConn.State And adStateOpen) Then objConn.Close

    Dim strPathToDB As String
                
    On Error GoTo ErrHandler:
            
    'set path according to live status
    If Range("Site").Value = "Office" Then
        strPathToFolder = Range("PathToFolderOffice").Value
    End If
    If Range("Site").Value = "Developer" Then
        strPathToFolder = Range("PathToFolderDev").Value
    End If
        
    If Range("LiveStatus").Value = "LIVE" Then
        strPathToDB = strPathToFolder + "\Database\VolMan.accdb"
    End If
    If Range("LiveStatus").Value = "Training" Or Range("LiveStatus").Value = "Dev" Then
        strPathToDB = strPathToFolder + "\Database\Training\VolManTraining.accdb"
    End If

    'MsgBox strPathToDB

    'open connection to Access database
    objConn.Open "Provider = Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & strPathToDB

    Exit Sub
    
ErrHandler:
    ' error handling code
    MsgBox "No connection to the VolMan database!" & vbCrLf & "Please call support"

End Sub

Sub DBConnectionClose()

    If CBool(objConn.State And adStateOpen) Then objConn.Close
    Set objConn = Nothing

End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Helen Feddema
Helen Feddema
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Helen. That's brilliant. I've been a big fan of your work over the years!

One thing. I'm developing on Excel and Access 2016. The users are on 2010. I realise that, having Referenced the Access 2016 library it won't work on the 2010. I understand I would need late binding. What's the modification to make it 'late binding' please?

Thanks.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks Helen. Thanks Rey. I adapted the code from both of you. Perfect.
Rey. I got from you how to do the Late Binding.
The rest of the code didn't run for some reason. It seemed to be looking for the .CSV file and not finding it there.
I used Helen's method of firing off the code in Access from Excel.
This is my final result (in Excel)

Public Sub ExportAccessTables_LATEBINDING()

    'Created by Helen Feddema 28-Jan-2017
    'Last modified by Helen Feddema 28-Jan-2017
    'Adapted Late Binding code from Rey Obrero on same Q on my E-E
    
    On Error GoTo ErrorHandler
    
    Dim strDBNameAndPath As String
    Dim appAccess As Object
    
    Set appAccess = CreateObject("Access.Application")
        
    'set path according to Site and Live Status
    If Range("Site").Value = "Office" Then
        strPathToFolder = Range("PathToFolderOffice").Value
    End If
    If Range("Site").Value = "Developer" Then
        strPathToFolder = Range("PathToFolderDev").Value
    End If
    
    'VolMan
    strDBNameAndPath = strPathToFolder + "\Database\VolMan.accdb"
    appAccess.OpenCurrentDatabase FilePath:=strDBNameAndPath, _
       exclusive:=False
    
    appAccess.Run "ExportTables"
    appAccess.CloseCurrentDatabase
    
    'VolMan_Log
    strDBNameAndPath = strPathToFolder + "\Database\VolMan_Log.accdb"
    appAccess.OpenCurrentDatabase FilePath:=strDBNameAndPath, _
       exclusive:=False

    appAccess.Run "ExportTables"
    appAccess.CloseCurrentDatabase
    
    MsgBox "Done!"
    
ErrorHandlerExit:
    Exit Sub
    
ErrorHandler:
    MsgBox "Error No: " & Err.Number _
       & " in ExportAccessTables procedure; " _
       & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

You got it -- just declare appAccess as Object, and use CreateObject.  It is such a pain to have to get code to run in multiple Office versions, and sometimes it just can't be done, because of shared objects, especially when using more than one Office component.  Good thing that hasn't happened here!