• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Backup MS Access Database into text File

Backup MS Access Database into text File

Backup MS Access Database into text File

API Declarations


TableName() As String
FName() As String
FType() As String
End Type
Public AListField() As ListField
Public csNewDatabase As ADODB.Connection
Public tsNewTable, tsRecordSet, tsNewRecord As ADODB.Recordset
Private strQuery, strQuery1 As String
Private iCount, iCount1, iCounter2 As Long




Rate Backup MS Access Database into text File

• 1
• 2
• 3
• 4
• 5

(2(2 Vote))

    Set csNewDatabase = New ADODB.Connection
    csNewDatabase.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;" & _
        "Data Source=" & sLongName & ";" & _
        "Persist Security Info=False"
    csNewDatabase.Open
    Set tsNewTable = csNewDatabase.OpenSchema(adSchemaTables)
    iCount = 0
    ReDim Preserve AListField(1)
    Do Until tsNewTable.EOF
      If (tsNewTable!Table_Type = "TABLE") And (Not Left(tsNewTable!table_Name, 11) = "MSysModules") Then
            Set tsNewRecord = New ADODB.Recordset
            tsNewRecord.Open "Select * from [" & tsNewTable!table_Name & "]", csNewDatabase, adOpenStatic, adLockOptimistic
            ReDim Preserve AListField(iCount)
            For iCount1 = 0 To tsNewRecord.Fields.Count - 1
                ReDim Preserve AListField(iCount).TableName(iCount1)
                ReDim Preserve AListField(iCount).FName(iCount1)
                ReDim Preserve AListField(iCount).FType(iCount1)
                AListField(iCount).TableName(iCount1) = tsNewTable!table_Name
                AListField(iCount).FName(iCount1) = tsNewRecord.Fields(iCount1).Name
                AListField(iCount).FType(iCount1) = tsNewRecord.Fields(iCount1).Type
            Next iCount1
            iCount = iCount + 1
      End If
      tsNewTable.MoveNext
    Loop
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile("c:\Testing.txt", True)
    For iCount = 0 To UBound(AListField)
        iCounter2 = UBound(AListField(iCount).TableName)
        Set tsRecordSet = New ADODB.Recordset
        tsRecordSet.Open "Select * From [" & AListField(iCount).TableName(0) & "]", csNewDatabase, adOpenStatic, adLockOptimistic
        Do While Not tsRecordSet.EOF
            strQuery = "Insert into " & AListField(iCount).TableName(0) & "("
            strQuery1 = ""
            For iCounter2 = 0 To UBound(AListField(iCount).TableName)
                strQuery = strQuery & "[" & AListField(iCount).FName(iCounter2) & "],"
                strQuery1 = strQuery1 & "'" & tsRecordSet.Fields(AListField(iCount).FName(iCounter2)).Value & "',"
            Next iCounter2
            strQuery = strQuery & ") VALUES (" & strQuery1 & ")" & vbCrLf
            a.Write (strQuery)
            tsRecordSet.MoveNext
        Loop
        ivProgress = (iCount / UBound(AListField)) * 100
    Next iCount
    a.Close
End Sub