vba - SQL Server 插入到Access中从 Access VBA中选择来自 Excel

  显示原文与译文双语对照的内容
91 5

我有一个 Access 2010数据库,所有表都链接到 SQL Server 2014表。我有一个(. xlsx ) 2010文件( 虽然它作为一个. csv, 开始,我可以让它保持不变),需要通过VBA代码导入到 SQL Server 表中。然而,我知道有可以用的导入工具,但每月有 20个文件导入,而且还有一个自动的方法。

All中的所有VBA代码都存在于Access数据库中,所有的VBA代码都可以找到,我可以从 Excel ( 例如 ) 中推送数据( ) 。这里代码在Excel中) 未通过访问( 即从Access中获取) 。代码正在访问中) 。

我更愿意用一个INSERT INTO AccessTable SELECT FROM ExcelRange查询而不是一行读取 Excel,我需要在Excel数据插入之前进行一些转换。

我目前拥有的是:

Private Sub TransferData(ByVal Company As String, ByVal Address As String, ByVal XLName As String)
Dim Con As ADODB.Connection
Dim SQLString As String
 SQLString ="INSERT INTO SatSurvey" & _
"(ClinicID, Method, CollectionDate, Duration, Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8, Q10, Q11, Q12, Q13, Q14, Q15, Q16, Q17, Q18, Q19, Q20, Q21, Q22, Physician, Nurse, MedAst)" & _
"SELECT" & _
"('clinic name', IDFormat, IDendDate, IDtime, Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8, Q10, Q11, Q12, Q13, Q14, Q15, Q16, Q17, Q18, Q19, Q20, Q21, Q22, NZ(Q9s1,1), NZ(Q9s2,1), NZ(Q9s3,1))" & _
" FROM [Excel 12.0;HDR=Yes;Database=" & XLName &"]." & Address
 Set Con = New ADODB.Connection
' Con.ConnectionString ="Datasource=" & CurrentProject.Connection
 Con.ConnectionString = CurrentProject.Connection
' Con.Properties(9).Value ="Extended Properties='Excel 12.0 Xml;HDR=YES';"
' Con.Provider ="Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0 Xml;HDR=YES';"
 Con.Open
 Con.Execute SQLString
End Sub

可以看到,我尝试了一些不同的选项来工作。这似乎是最好的选择,除了我现在正在获取的:

运行时错误'-2147467259 ( 80004005 ):
数据库已经被用户'管理员'上的用户置于状态,防止它被打开或者锁定。

指示的机器名称为我的计算机由于代码从我的机器上的访问中运行,因这里似乎我已经经将数据库打开了。

我不是这样做的,因这里任何修复或者备用建议都是欢迎的。

时间:原作者:0个回答

147 5

你无需创建连接到你当前在访问会话中打开的数据库的New ADODB.Connection你的Con 对象变量可以简单地引用 CurrentProject.Connection

Set Con = CurrentProject.Connection

但是,你甚至不需要对象变量。直接从 CurrentProject.Connection 中使用 Execute 方法。

CurrentProject.Connection.Execute SQLString

比你现在拥有的要简单,但我不确定它是否能消除的。防止它被打开或者锁定"投诉。

自从你说 "欢迎使用其他建议",考虑DAO而不是 ADO 。并且不要在 INSERT 查询( 无论你选择的是ADO还是 DAO )的SELECT 部分的字段表达式列表中包含括号。

Private Sub TransferData(ByVal Company As String, ByVal Address As String, ByVal XLName As String)
 Dim SQLString As String
 SQLString ="INSERT INTO SatSurvey" & _
"(ClinicID, Method)" & _
"SELECT 'NTN - Off-site', IDFormat" & _
" FROM [Excel 12.0;HDR=Yes;Database=" & XLName &"]." & Address
 Debug.Print SQLString
 CurrentDb.Execute SQLString, dbFailOnError
End Sub
原作者:
62 1

下面的链接中可以找到几种将数据从Excel移到 SQL Server的方法。

http://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm

http://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm#Excel%20Data%20Export%20to%20SQL%20Server%20Test%20Code

很明显你从Excel里。另外,考虑使用 SQL Server ( 与上面描述的相反) 遍历文件。

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=10000)
BEGIN
PRINT @intFlag
declare @fullpath1 varchar(1000)
select @fullpath1 = '''FTPyour_path' + convert(varchar, getdate()- @intFlag, 112) + '_your_file.txt'''
declare @cmd1 nvarchar(1000)
select @cmd1 = 'bulk insert [dbo].[your_table] from ' + @fullpath1 + ' with (FIELDTERMINATOR = ''t'', FIRSTROW = 2, ROWTERMINATOR=''0x0a'')'
exec (@cmd1)
SET @intFlag = @intFlag + 1
END
GO

这里脚本假定文件的名称为文件的名称。希望你的Excel文件在文件名中有某种类型的Pattern,你可以以利用。

原作者:
139 3

我发现通过实际链接或者从Excel导入表来实现( 调试) 比较容易。然后就可以像其他表格一样使用它。

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12Xml,"TempImport", XLName, True
' If the range is needed, linking doesn't work. Use import instead.
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml,"TempImport", XLName, True, Address
SQLString ="INSERT INTO SatSurvey.. . SELECT.. . FROM TempImport"
CurrentDb.Execute SQLString
' Clean up temp table
DoCmd.DeleteObject acTable,"TempImport"
原作者:
...