MS Access VBA将表导出为文本文件

Question

我正在使用下面的代码将数据从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');

任何想法。

Answer 1

使用数组和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

Answer 2

请尝试更改此行：

sText = "('" & rst!CityID & "','" & rst!City & "','0'),"

带有：

If rst.EOF then
sText = "('" & rst!CityID & "','" & rst!City & "','0');"
Else
sText = "('" & rst!CityID & "','" & rst!City & "','0'),"
End If