Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Posts
    2

    Unanswered: Error in Import/Export to text file from Access

    hi,

    Here is my code of Export/Import Utility of text file to Access and vice versa. Export access table to text file successful but when import text file to Access tabel it gives error "Microsof Access can't open the database because it is missing, or opened exclusively by another user"

    Public Sub ExportToTextFile(sDBName As String, strQuery As String, sFileNameWithPath As String, Optional blnOpenFile As Boolean = False)
    '------------------------------------------------------------------------------
    '-- Description: Create and display an Text file based on received query
    '-- Received: the query to be applied and exported
    '-- Returned: none
    '-- Comments:
    '------------------------------------------------------------------------------

    Dim appAccess As Access.Application ' Used to transfer the Query to Spreadsheet
    Dim db As DAO.Database ' Database object
    'Dim strDBName As String ' Database Name
    Dim strFile As String ' Name of Generated Text File

    On Error GoTo LOCAL_ERR

    If sFileNameWithPath = "" Then
    MsgBox "FileName can't be blank", vbInformation, "WMS"
    Exit Sub
    End If

    ' what follows is a fix so it wont ask password later
    Set appAccess = New Access.Application
    Set db = appAccess.DBEngine.OpenDatabase(sDBName, False, False) ' ";PWD=" & DB_PW)
    appAccess.OpenCurrentDatabase sDBName
    db.Close
    Set db = Nothing

    On Error GoTo 0 ' Reset error handling
    On Error Resume Next ' so it wont give an error if the query dont exist

    ' Delete the last query
    appAccess.DBEngine.Workspaces(0).Databases(0).Quer yDefs.Delete ("EXPORT")

    On Error GoTo 0 ' reset the error handling to "default"

    ' Then Create the query in the access database
    appAccess.DBEngine.Workspaces(0).Databases(0).Crea teQueryDef _
    "EXPORT", strQuery

    ' delete the old Text file
    On Error Resume Next ' so it wont give an error if the file dont exist
    Kill sFileNameWithPath
    On Error GoTo 0 ' reset the error handling to "default"

    On Error GoTo LOCAL_ERR

    ' Finally make the transfer
    appAccess.DoCmd.TransferText acExportDelim, , "EXPORT", sFileNameWithPath, True

    ' close access instance
    appAccess.Quit acQuitSaveNone

    Set appAccess = Nothing

    ' If blnOpenFile Then
    ' ' Open Excel with exported file
    ' Shell GetFilePath("C:\", "excel.exe") & " " & sfilenamewithpath _
    ' , vbMaximizedFocus
    ' End If

    Exit Sub

    LOCAL_ERR:
    'DisplayErrMsg "ExportToText"
    MsgBox Err.Description, vbInformation, "ExportToTextFile"
    End Sub


    Public Sub ImportFromTextFile(sDBName As String, sTableName As String, sFileNameWithPath As String, Optional blnOpenFile As Boolean = False)
    '------------------------------------------------------------------------------
    '-- Description: Import from Text File to Microsoft Access Table
    '-- Received: ImportFromTextFileName, ToDatabaseName, ToTableName
    '-- Returned: none
    '-- Comments:
    '------------------------------------------------------------------------------

    Dim appAccess As Access.Application ' Used to transfer the Query to Spreadsheet
    Dim db As DAO.Database ' Database object

    On Error GoTo LOCAL_ERR

    If sFileNameWithPath = "" Then
    sFileNameWithPath = App.Path & "\Import.txt"
    End If

    ' what follows is a fix so it wont ask password later
    Set appAccess = New Access.Application
    Set db = appAccess.DBEngine.OpenDatabase(sDBName, False, False) ' ";PWD=" & DB_PW)
    appAccess.OpenCurrentDatabase sDBName
    db.Close
    Set db = Nothing

    On Error GoTo 0 ' Reset error handling

    On Error GoTo LOCAL_ERR

    ' Finally make the transfer
    appAccess.DoCmd.TransferText acImportDelim, , sTableName, sFileNameWithPath, True

    ' close access instance
    appAccess.Quit acQuitSaveNone

    Set appAccess = Nothing

    ' If blnOpenFile Then
    ' ' Open Excel with exported file
    ' Shell GetFilePath("C:\", "excel.exe") & " " & sfilenamewithpath _
    ' , vbMaximizedFocus
    ' End If

    MsgBox "Import " & sTableName & " Successful"
    Exit Sub

    LOCAL_ERR:
    'DisplayErrMsg "ExportToExcel"
    MsgBox Err.Description, vbInformation, "ImportFromTextFile"
    End Sub


    Regards
    Pratik Padia

  2. #2
    Join Date
    Dec 2002
    Posts
    18
    Somthing, maybe it is the error message, tells me that you shouldn't close the database when importing the textfile.
    It's just a hunch, but I don't have time for more, hope it helps

Posting Permissions

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