如何设置Application.CurrentProject.Connection在ACCDB项目？

问题描述

我们正在将ADP项目转换为ACCDB，因为Office 2013版本中不再支持ADP。

我有以下代码在 ADP 中更改应用程序连接：

  ChangeADPConnection（strServerName As String，strDBName As _ 
 String，可选strUN As String，可选strPW As String）作为布尔
 Dim strConnect As String 
出现错误GoTo EH：
应用程序。 CurrentProject.CloseConnection 
'提供程序，数据源和初始目录参数是必需的。 
 strConnect =Provider = SQLOLEDB.1& _ 
; Data Source =& strServerName& _ 
;初始目录=& strDBName 
如果strUN<> then 
 strConnect = strConnect& ; user id =& strUN 
如果strPW<> then 
 strConnect = strConnect& ; password =& strPW 
 End If 
 Else'如果没有提供用户名，请尝试使用集成安全性。 
 strConnect = strConnect& ;集成安全性= SSPI
结束如果
 Application.CurrentProject.OpenConnection strConnect 
 ChangeADPConnection = True 
退出函数
 EH：
 MsgBox Err.Number & ：& Err.Description，vbCritical，Connection Error
 ChangeADPConnection = False 
结束函数
  

但是，我不知道如何更改当前项目连接在ACCDB？

我使用

<$>

p $ p> 公共函数ChangeACCDBConnection（strServerName As String，strDBName As _
String，可选strUN As String，可选strPW As String）作为布尔

Dim strConnect As String
Dim cnn As ADODB.Connection
Application.CurrentProject.Connection.Close
出现错误GoTo EH：
strConnect =XXXXX
设置Con = New ADODB。连接
Con.ConnectionString = strConnect
CurrentProject.OpenConnection strConnect //此处的错误
ChangeConnection = True
退出函数

EH：
MsgBox错误编号& ：& Err.Description，vbCritical，Connection Error
ChangeConnection = False
结束函数


我收到错误，更改当前项目连接。

唯一的问题是如何设置连接到Application.CurrentProject以便使用它

解决方案

据我所知，在一个标准的Access数据库应用程序（不是一个ADP），你可以操纵你的连接只有这些方式：

1. 删除链接表并使用代码重新创建这些链接。如果你这样做，你可以链接/重链接到任何有效的数据源。这对任何ADO连接都没有影响。链接表实际上使用DAO和ODBC的组合作为数据访问层。


2. 在代码模块中创建一个ADO连接对象作为全局变量。写入功能以在要切换数据源时更改其连接。 （你也可以在类中包装所有这些。）

我不认为非ADP应用程序有一个内置全局ADO连接像ADP，所以你必须使用上面列出的方法。

we are in the process of converting an ADP project into ACCDB as ADP's are no longer supported in Office 2013 version.

I have the below code to change the application connection in ADP :

Function ChangeADPConnection(strServerName As String, strDBName As _
   String, Optional strUN As String, Optional strPW As String) As Boolean
Dim strConnect As String
On Error GoTo EH:
Application.CurrentProject.CloseConnection
'The Provider, Data Source, and Initial Catalog arguments are required.
strConnect = "Provider=SQLOLEDB.1" & _
";Data Source=" & strServerName & _
";Initial Catalog=" & strDBName
If strUN <> "" Then
    strConnect = strConnect & ";user id=" & strUN
    If strPW <> "" Then
        strConnect = strConnect & ";password=" & strPW
    End If
Else  'Try to use integrated security if no username is supplied.
    strConnect = strConnect & ";integrated security=SSPI"
End If
Application.CurrentProject.OpenConnection strConnect
ChangeADPConnection = True
Exit Function
EH:
MsgBox Err.Number & ": " & Err.Description, vbCritical, "Connection Error"
ChangeADPConnection = False
End Function

But , I am not sure how to change the current project connection in ACCDB ?

I am using linked tables with SQL Server as backend

Here is what i have tried :

Public Function ChangeACCDBConnection(strServerName As String, strDBName As _
   String, Optional strUN As String, Optional strPW As String) As Boolean

    Dim strConnect As String
    Dim cnn As ADODB.Connection
    Application.CurrentProject.Connection.Close
    On Error GoTo EH:
    strConnect = "XXXXX"
    Set Con = New ADODB.Connection
    Con.ConnectionString = strConnect
    CurrentProject.OpenConnection strConnect // Error here
    ChangeConnection = True
    Exit Function

EH:
    MsgBox Err.Number & ": " & Err.Description, vbCritical, "Connection Error"
    ChangeConnection = False
End Function

I am getting an error ,change the current project connection. The connection string is fine and it is connecting sucessfully to the database.

The only thing is how to set that connection to Application.CurrentProject inorder to use it acrosss the application.

Any help much appreciated..

解决方案

As far as I know, in a standard Access database application (not an ADP) you can manipulate your connection(s) in only these ways:

1. Delete linked tables and recreate those links using code. If you do this correctly you can link/relink to any valid data source. This has no affect though on any ADO connections. Linked tables actually use a combination of DAO and ODBC as the data access layers.
2. Create an ADO connection object as a global variable in a code module. Write functions to change its connection when you want to switch data sources. (You could also wrap all this in a class.)

I don't think a non-ADP application has a built-in global ADO connection like ADP so you have to use the methods listed above.

这篇关于如何设置Application.CurrentProject.Connection在ACCDB项目？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！