在Access VBA中从Excel插入到Access Select中 [英] Insert into Access Select from Excel from within Access VBA

问题描述

我有一个Access 2010数据库，所有表都链接到SQL Server 2014表.我有一个Excel 2010(.xlsx)文件(尽管它以.csv开头，并且我可以这样离开)，我需要通过VBA代码导入到SQL Server表中.我知道有可用的导入工具来执行此操作，但是，我每个月要导入20多个XLS文件，而宁愿有一种自动的方法来执行此操作.

I have an Access 2010 database with all tables linked to SQL Server 2014 tables. I have an Excel 2010 (.xlsx) file (though it starts as a .csv, and I could leave it that way), that I need to import into a SQL Server table via VBA code. I know that there are import tools available to do this, however, I have 20+ XLS files per month to import, and would rather have an automated method of doing this.

我所有的VBA代码都驻留在Access数据库中，而我能够找到的所有VBA代码示例都是用于从Excel中推送 数据(即，代码在Excel中)而不是<从Access中em

All my VBA code resides in the Access database, and all the examples of VBA code I've been able to find are for pushing data from Excel (i.e. the code is in Excel) not pulling from Access (i.e. the code is in Access).

我宁愿使用单个INSERT INTO AccessTable SELECT FROM ExcelRange查询来执行此操作，而不是逐行读取Excel，并且需要在插入Excel数据之前对其进行一些转换.

I would prefer to do it with a single INSERT INTO AccessTable SELECT FROM ExcelRange query instead of reading Excel row by row, and I need to do some transformation on the Excel data before it's inserted.

到目前为止我所拥有的:

What I have so far:

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

如您所见，我尝试了几种不同的方法来使事情正常进行.这似乎是我现在得到的最好的 :

As you can see, I've tried a few different options to get things working. This seems to work the best except that I am now getting:

运行时错误'-2147467259(80004005)':
该数据库已由用户'MINIT-EGHJSAU'上的管理员'Admin'置于一种状态，导致该数据库无法打开或锁定.

Run-time error '-2147467259 (80004005)':
The Database has been placed in a state by user 'Admin' on machine 'MINIT-EGHJSAU' that prevents it from being opened or locked.

指示的机器名称是 my 机器.由于代码是在我的计算机上的Access中运行的，因此我应该打开数据库似乎是合乎逻辑的.

The machine name indicated is my machine. Since the code is running from within Access on my machine, it seems logical that I would have had the database open.

我不受此限制，因此欢迎提供任何修复程序或替代建议.

I'm not tied to doing it this way, so any fixes or alternate suggestions are welcome.

推荐答案

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

You don't need to create a New ADODB.Connection which connects to the database you currently have open in your Access session. Your Con object variable can simply reference CurrentProject.Connection ...

Set Con = CurrentProject.Connection

但是，您甚至根本不需要该对象变量.只需直接从CurrentProject.Connection ...

However, you don't really even need that object variable. Just use the Execute method directly from CurrentProject.Connection ...

CurrentProject.Connection.Execute SQLString

这比您现在拥有的要简单，但是我不确定它是否会消除状态...阻止其打开或锁定"的投诉.

That is simpler than what you have now, but I'm not certain it will eliminate the "state ... that prevents it from being opened or locked" complaint.

由于您说过欢迎其他建议" ，因此请考虑使用DAO而不是ADO.并且不要在INSERT查询的SELECT段中的字段表达式列表周围包含括号(无论您选择ADO还是DAO).

Since you said "alternate suggestions are welcome", consider DAO instead of ADO for this. And do not include parentheses around the field expression list in the SELECT piece of your INSERT query (regardless of whether you choose ADO or DAO).

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 '<location>', IDFormat " & _
                " FROM [Excel 12.0;HDR=Yes;Database=" & XLName & "]." & Address
    Debug.Print SQLString
    CurrentDb.Execute SQLString, dbFailOnError
End Sub

这篇关于在Access VBA中从Excel插入到Access Select中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！