VBA编写的访问从SQL Server数据到Access数据库 [英] VBA Access writing data from SQL server to the Access DB

问题描述

我试着去打开一个SQL存储过程，它包含一个选择前5 *形式的表，将数据加载到表3名为Access表。 如何设置命令对象ActiveConnection属性使用当前的Access数据库？ 当我为它提供一个实际的连接字符串，它说，用户已锁定它。 此刻，它运行并打印打印出的结果，但它不插入的值。它不给我一个错误或者。

 使用此code运行SP和提取所有记录
公用Sub ExecuteSPAsMethod2（）
昏暗rsData作为ADODB.Recordset
昏暗sConnectSQL作为字符串'创建连接到SQL Server
昏暗sConnectAccess作为字符串'创建与Access数据库连接（可能没有neccessary）
昏暗objCommand作为ADODB.Command'对SP的INSERT结果到Access表


创建连接字符串到SQL Server
sConnectSQL =供应商= SQLOLEDB;数据源= MYSERVER;＆放大器; _
初始目录= SQLDatabase;集成安全性= SSPI

创建Connection对象和Recordset对象
设置objConn =新ADODB.Connection
设置rsData =新ADODB.Recordset

打开连接
objConn.Open sConnectSQL
执行SP和得到的结果到记录
objConn.SurveyDataSP4，rsData

做，而不是rsData.EOF
Debug.Print rsData！阶层
rsData.MoveNext
循环

现在将数据写入访问表
创建连接字符串来访问数据库表
sConnectAccess =供应商= Microsoft.ACE.OLEDB.12.0; ＆放大器; _
                数据源= C：\ Databse1.accdb; ＆放大器; _
                模式=分享独家

用于访问SQL查询'命令对象
设置objCommand =新ADODB.Command
objCommand.ActiveConnection = sConnectAccess

在数据库中插入新记录
加载SQL字符串到命令对象
做，而不是rsData.EOF
objCommand.CommandText =INSERT INTO表3（＆放大器;！rsData阶层和放大器;）
objCommand.Execute
rsData.MoveNext
循环


结束小组
 

解决方案

有没有必要写这样大量的code和创建世界贫困。保存执行命令作为传递查询。

例如：

  Exec的4
 

假设上述被称为SP1，那么这个code将追加的所有数据从上面到本地表：

  CurrentDb.ExecuteINSERT INTO sp1Local从SP1选择SP1。*
 

所以，这一切code可以用一行VBA code来完成。

Im trying to open a SQL stored procedure, which contains a Select top 5* form table, and load the data into an Access table called Table3. How do I set the Command Object ActiveConnection property to use the current Access DB? when I provide it with an actual connection string, it says that the user has locked it. At the moment, it runs and prints prints out the results but it does not insert the values. It does not give me an error either.

'Use this code to run the SP and extract all the records
Public Sub ExecuteSPAsMethod2()
Dim rsData As ADODB.Recordset
Dim sConnectSQL As String 'to create connection to SQL Server
Dim sConnectAccess As String 'to create connection with Access DB (may not be neccessary)
Dim objCommand As ADODB.Command 'for INSERT results of SP into Access table


'Creating the connection string to SQL server
sConnectSQL = "Provider=SQLOLEDB;Data Source=MYSERVER; " & _
"Initial Catalog=SQLDatabase;Integrated Security=SSPI"

'Creating the Connection object and Recordset object
Set objConn = New ADODB.Connection
Set rsData = New ADODB.Recordset

'Opening the connection
objConn.Open sConnectSQL
'Execute the SP and give the results to the Recordset
objConn.SurveyDataSP "4", rsData

Do While Not rsData.EOF
Debug.Print rsData!Stratum
rsData.MoveNext
Loop

'Now write the data into Access Table
'Create connection string to Access DB table
'sConnectAccess = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=C:\Databse1.accdb;" & _
                "Mode = Share Exclusive"

'Command object to be used for Access SQL query
Set objCommand = New ADODB.Command
'objCommand.ActiveConnection = sConnectAccess

'Insert new record in the DB
'Load the SQL string into the command object
Do While Not rsData.EOF
objCommand.CommandText = "INSERT INTO table3 (" & rsData!Stratum & ")"
objCommand.Execute
rsData.MoveNext
Loop


End Sub

解决方案

There is no need to write such large amounts of code and create world poverty. Save the execute command as a pass through query.

Eg:

Exec 4

Assuming the above is called sp1, then this code will append all data from the above into the local table:

CurrentDb.Execute "INSERT INTO sp1Local select sp1.* from sp1"

So all of this code can be done with ONE line of VBA code.

这篇关于VBA编写的访问从SQL Server数据到Access数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！