by Dwi Chrissanto (1 Submission)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Sat 3rd August 2002
Date Added: Mon 8th February 2021
Rating: (1 Votes)
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
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