adodb连接和访问db的链接

问题描述

我使用vba代码在ACCESS和远程SQL DB之间建立了adodb连接。代码是用Access模块​​编写的（检查如下，它可以工作）。现在的问题是：我需要将student_table数据从SQL DB导入到Access数据库本地表，但我没有任何
的想法如何链接它并在Access数据库中显示该student_table。提前感谢您的帮助。



$
'连接代码



公开Sub runQuery2（）



    Dim conn As ADODB.connection

    Dim rs As ADODB.Recordset

    Dim sConnString As String



    sConnString =" Provider = SQLOLEDB.1; Password = ******; Persist Security Info = True; User ID = *****; Initial Catalog = ******; Data Source = *** ****;使用准备程序= 1;自动翻译=真;数据包大小= 4000;工作站ID = ****;使用加密
数据=假;可能时使用列排序标记= False"

    



    '创建Connection和Recordset对象。

   设置conn =新ADODB.connection

   设置rs =新ADODB.Recordset



$
    conn.CommandTimeout = 0

  

    conn.Open sConnString

    

   设置rs = conn.Execute（" SELECT * from Student_Table"）



'检查数据

'如果不是rs.EOF那么rs.MoveFirst

'Do until rs.EOF

'    Debug.Print rs！名称

'    rs.MoveNext

'循环

MsgBox" connected"

  

End Sub

Hi,

I have made a adodb connection between ACCESS and remote SQL DB using vba code. Code is written in Access modules (check below, it works). Now the question is: I need to import student_table data from SQL DB to Access database local table but I don't have any idea how it can be linked and show up that student_table in Access database. Appreciate your help in advance.


'connection code

Public Sub runQuery2()

    Dim conn As ADODB.connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String

    sConnString = "Provider=SQLOLEDB.1;Password=******;Persist Security Info=True;User ID=*****;Initial Catalog=******;Data Source=*******;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4000;Workstation ID=****;Use Encryption for Data=False;Tag with column collation when possible=False"
    

    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.connection
    Set rs = New ADODB.Recordset


    conn.CommandTimeout = 0
  
    conn.Open sConnString
    
    Set rs = conn.Execute("SELECT * from Student_Table ")

'checking data
'If Not rs.EOF Then rs.MoveFirst
'Do Until rs.EOF
'    Debug.Print rs!Name
'    rs.MoveNext
'Loop
MsgBox "connected"
  
End Sub

推荐答案

不太确定您的目标。 是否要导入表并在Access中创建不再链接到原始SQL的本地副本，或者是否要链接SQL表？

Not quite sure what you are after.  Do you want to import the table and create a local copy in Access that is no longer linked to the original SQL one or do you want to link the SQL table?

查看Docmd.TransferDatabase方法，它允许您执行上述任一选项。

Have a look at the Docmd.TransferDatabase method, it allows you to do either one of the options explained above.

干杯，

Vlad

这篇关于adodb连接和访问db的链接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！