时间戳

首页 > 解决方案 > MS Access VBA 导出表到文本文件

vba - MS Access VBA 导出表到文本文件

问题描述

我正在使用下面的代码将数据从 Access 表导出到文本文件，以便我可以在 mySQL 中使用它。

我的出口代码：

Sub ExpTblCity()
On Error GoTo Err_Handler
Dim t, sText, rText, LResult As String

Close #1
t = "INSERT INTO `tblcity` (`city_id`,`city_name`,`city_enabled`) VALUES "

Dim rst As DAO.Recordset
Open Application.CurrentProject.Path & "\2-TblCity.txt" For Output As #1
Print #1, t

Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblCity", dbOpenSnapshot)
Do While Not rst.EOF
rText = "'NULL'"
sText = "('" & rst!CityID & "','" & rst!City & "','0'),"

LResult = Replace(sText, rText, "NULL")
    Print #1, LResult
    rst.MoveNext
Loop

rst.Close
Set rst = Nothing

t = ""
sText = ""
rText = ""
LResult = ""

Close #1

Exit_This_Sub:
    Exit Sub
Err_Handler:
    If Err = 0 Then
    ElseIf Err = 94 Then
        Resume Next
    ElseIf Err = 3265 Then
        Resume Next
    Else
        MsgBox "Error #: " & Err.Number & " " & Err.Description
    End If
Resume Exit_This_Sub
End Sub

上述代码的输出：

INSERT INTO `tblcity` (`city_id`,`city_name`,`city_enabled`) VALUES 
('1','London','0'),
('2','Paris','0'),
('3','Rome','0'),
('4','Athens','0'),
('5','Madrit','0'),

代码运行良好，但我试图将最后一行替换为; .

正确的输出：

...
('4','Athens','0'),
('5','Madrit','0');

任何的想法。

标签： vbams-access

解决方案

使用数组保存并使用 Join 函数会很方便。

Sub ExpTblCity()
    On Error GoTo Err_Handler
    Dim t As String, sText As String, rText As String, LResult As String
    Dim vResult() As Variant
    Dim n As Long
    Close #1
    t = "INSERT INTO `tblcity` (`city_id`,`city_name`,`city_enabled`) VALUES "
    
    Dim rst As DAO.Recordset
    
    Open Application.CurrentProject.Path & "\2-TblCity.txt" For Output As #1
    Print #1, t
    
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblCity", dbOpenSnapshot)
    Do While Not rst.EOF
        n = n + 1
        rText = "'NULL'"
        sText = "('" & rst!CityID & "','" & rst!City & "','0')"
        ReDim Preserve vResult(1 To n)
        
        sText = Replace(sText, rText, "NULL")
         vResult(n) = sText
        'Print #1, LResult
        rst.MoveNext
    Loop
    
    sText = Join(vResult, "," & vbCrLf) & ";"
    Print #1, sText
    rst.Close
    Set rst = Nothing
    
    t = ""
    sText = ""
    rText = ""
    LResult = ""
    
    Close #1
    
Exit_This_Sub:
        Exit Sub
Err_Handler:
        If Err = 0 Then
        ElseIf Err = 94 Then
            Resume Next
        ElseIf Err = 3265 Then
            Resume Next
        Else
            MsgBox "Error #: " & Err.Number & " " & Err.Description
        End If
    Resume Exit_This_Sub
End Sub

推荐阅读