vba读取服务器accsess文件,VB6批量读取CSV文件到access?

爱reo樱桃的纱雾酱

于 2021-08-11 23:16:00 发布

阅读量743

点赞数

文章标签： vba读取服务器accsess文件

该博客展示了如何使用VBA在VB6环境中通过Microsoft ActiveX Data Objects (ADODB) 连接Access数据库，并通过联合查询的方式批量读取CSV文件中的数据，将其插入到Access数据表中。

摘要由CSDN通过智能技术生成

Sub 联合查询后导入() 'VBA中测试通过

'引用Microsoft ActiveX Data Objects 2.x Library

'VB6批量读取CSV文件到access?

'http://club.excelhome.net/thread-973334-1-1.html

Dim xlapp As Object

Dim cnn As New ADODB.Connection

Dim myPath$, MyFile$, SQL$

Set xlapp = GetObject(, "Excel.Application") 'VB6中

myPath = xlapp.ActiveWorkbook.Path & ""

cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & myPath & "Database.accdb" '连接数据库

MyFile = Dir(myPath & "*.csv")

Do While MyFile <> "" '创建多个文本文件的联合查询

If Len(SQL) Then

SQL = SQL & " union all select '" & Mid(MyFile, 10, 7) & "' as FormID,'" & Mid(MyFile, 1, 8) & "' as FormType,f1 as ModelName,f4 as ModelVersion,f3 as OutPutQty,f2 as ProductionLine from [Text;FMT=Delimited;HDR=no;DATABASE=" & myPath & ";].[" & MyFile & "]"

Else

SQL = "select '" & Mid(MyFile, 10, 7) & "' as FormID,'" & Mid(MyFile, 1, 8) & "' as FormType,f1 as ModelName,f4 as ModelVersion,f3 as OutPutQty,f2 as ProductionLine from [Text;FMT=Delimited;HDR=no;DATABASE=" & myPath & ";].[" & MyFile & "]"

End If

MyFile = Dir()

Loop

SQL = "insert into BackflushRecord select * from (" & SQL & ")" '向access数据表中添加数据语句

cnn.Execute SQL

MsgBox "已经成功将文本文件数据保存为数据库！", vbInformation

cnn.Close

Set cnn = Nothing

End Sub