通过VB.NET在Access中重新链接（ODBC）表和查询 [英] Relink (ODBC) tables and query's in Access via VB.NET

问题描述

我想在VB.NET中创建一个小应用程序来重新链接Access数据库中的所有查询'

和表通过ODBC链接到SQL Server。

它必须能够重新链接所有表并查询给定的数据库，在给定sql server的情况下给
a给出登录名和密码。


Aybody知道怎么做，或者更好：有一个示例应用程序这样做吗？


提前致谢！


Pieter

Hi,

I want to make a small application in VB.NET that relinks all the query''s
and tables in an Access database that are linked via ODBC to an SQL Server.
It must be able to relink all the tables and query''s to a given databse, on
a given sql server with given login and password.

Aybody knows how to do that, or better: has a sample application doing this?

Thanks in advance!

Pieter

推荐答案

嗨彼得，


我正在写一些东西要求你更准确，当我想要的时候

就是你要找的东西。

http://www.connectionstrings.com/
http://www.able-consulting.com/ADO_Conn.htm

$ b $我请不要更精确一些吗？


Cor

Hi Pieter,

I was writing something to ask you to be more precise, when I thougth maybe
is this what you are looking for.

http://www.connectionstrings.com/

http://www.able-consulting.com/ADO_Conn.htm

I not be something more precise please?

Cor

我想在VB.NET中创建一个重新链接所有应用程序的小应用程序Access数据库中的查询'和
以及通过ODBC链接到SQL
服务器的表。它必须能够重新链接所有表并使用给定的登录名和密码在给定的sql server上查询给定数据库
。

Aybody知道如何做到这一点，或者更好：有一个示例应用程序在这个吗？b / b这是什么？

I want to make a small application in VB.NET that relinks all the query''s
and tables in an Access database that are linked via ODBC to an SQL Server. It must be able to relink all the tables and query''s to a given databse, on a given sql server with given login and password.

Aybody knows how to do that, or better: has a sample application doing this?

不，它不是那样的：我需要的是一些改变链接的东西表格

和查询'，而不是连接到它们。我在Access中有它，但不是在
VB.NET中。 （见下文）。我只是在寻找下面

代码的''翻译'：


选项比较数据库

选项明确

'' - 全局连接字符串 -

全局Const oudeDSN =" ODBC; DSN = OldOdbcName"

Private intLengte As Integer

Global Const iDSN =" OdbcName"

Global Const iSRVR =" ServerName"
Global Const iDATABASE =" DatabaseName"

Global Const iUID =" UserId"

Global Const iPWD =" Password"

Type TableDefinition

name As String

SourceTableName As String

结束类型

全局arrTbl（）如表定义


函数RefreshAttach （）

DoCmd.Hourglass True

''-------------------------- ---

'' - 功能刷新附件 -

''-------------------- ---------

On Error Resume Next

Dim ldb作为数据库

Dim tdf As TableDef

Dim qdf As QueryDef

Dim wrktdf As String

Dim wrksrc As String

Dim r As Integer

Dim s As Integer

Dim varReturn As Variant

''返回参考当前数据库。

设置ldb = CurrentDb

s = -1

擦除arrTbl


intLengte = Len（oudeDSN）


for r = 0 to ldb.TableDefs.Count - 1

''Keep Params

If（ ldb.TableDefs（r）.Attributes和dbAttachedODBC）和

（左（ldb.TableDefs（r）.Connect，intLengte）= oudeDSN）然后

s = s + 1

ReDim Preserve arrTbl（0 to s）

arrTbl（s）.name = ldb.TableDefs（r）.name

arrTbl（s ）.SourceTableName = ldb.TableDefs（r）.SourceTableName

结束如果

下一个r

对于r = 0到s

varReturn = SysCmd（acSysCmdSetStatus，" Attachching Table" &

arrTbl（r）.name）

ldb.TableDefs.Delete arrTbl（r）.name

ldb.TableDefs.Refresh

设置tdf = ldb.CreateTableDef（arrTbl（r）.name，dbAttachSavePWD）

tdf.SourceTableName = arrTbl（r）.SourceTableName

tdf .Connect =" ODBC; DSN =" &安培; iDSN&英寸; SRVR = QUOT; &安培; iSRVR&英寸; DATABASE =" &

iDATABASE&英寸; UID =" &安培; iUID&英寸; PWD =" &安培; iPWD&英寸; TABLE = QUOT; &

arrTbl（r）.SourceTableName& "

On Error Resume Next

ldb.TableDefs.Append tdf

On Error GoTo RefreshAttach_Err

ldb.TableDefs.Refresh

设置tdf =没什么

下一个r

擦除arrTbl

s = -1

对于r = 0到ldb.QueryDefs.Count - 1

''保持参数

If（ldb.QueryDefs（r）.Type = dbQSQLPassThrough ）和

（左（ldb.QueryDefs（r）.Connect，intLengte）= oudeDSN）然后

s = s + 1

ReDim Preserve arrTbl（0 to s）

arrTbl（s）.name = ldb.QueryDefs（r）.name

结束如果

下一个r


对于r = 0到s

varReturn = SysCmd（acSysCmdSetStatus，附加查询&

arrTbl（r ）.name）

设置qdf = ldb.QueryDefs（arrTbl（r）.name）

qdf.Connect =" ODBC; DSN =" &安培; iDSN&英寸; SRVR = QUOT; &安培; iSRVR&英寸; DATABASE =" &

iDATABASE&英寸; UID =" &安培; iUID&英寸; PWD =" &安培; iPWD& ""

ldb.QueryDefs.Refresh

设置qdf =没什么

下一页r


设置ldb = Nothing


RefreshAttach_End：

varReturn = SysCmd（acSysCmdSetStatus，""）

DoCmd.Hourglass False

退出功能


RefreshAttach_Err：

''显示错误信息。

DoCmd.Hourglass False

MsgBox"错误号码" &安培; Err.Number& ： &安培; Err.Description

''在发生错误后继续声明。

DoCmd.Hourglass True

恢复RefreshAttach_End：

结束函数


函数AttachRemoveDbo（）作为整数

错误GoTo RemoveDbo_err

Dim CurrentDatabase As Database

Dim MyDocument As Document

Dim J As Integer

设置CurrentDatabase = DBEngine（0）（0）

对于J = 0 To CurrentDatabase.Containers（" Tables"）。Documents.Cou nt - 1

Set MyDocument = CurrentDatabase.Containers（" Tables"）。Documents（J）

如果Left（MyDocument.name，4）=" dbo_"然后

DoCmd.Rename Mid（MyDocument.name，5），A_TABLE，MyDocument.name

结束如果

下一个J

AttachRemoveDbo = True

AttachRemoveDbo_exit：

退出函数

AttachRemoveDbo_err：

MsgBox错误

AttachRemoveDbo = False

恢复AttachRemoveDbo_exit

结束功能


" Cor" < no*@non.com>在消息中写道

新闻：＃5 ************** @ TK2MSFTNGP09.phx.gbl ...

No, it''s not that: what I need is something that refeshes the linked tables
and query''s, not that connects to them. I have it in Access, but not in
VB.NET. (See underneath this). I''m just looking for a ''translation'' of the
code below:

Option Compare Database
Option Explicit
''-- Global Connection Strings --
Global Const oudeDSN = "ODBC;DSN=OldOdbcName"
Private intLengte As Integer
Global Const iDSN = "OdbcName"
Global Const iSRVR = "ServerName"
Global Const iDATABASE = "DatabaseName"
Global Const iUID = "UserId"
Global Const iPWD = "Password"
Type TableDefinition
name As String
SourceTableName As String
End Type
Global arrTbl() As TableDefinition

Function RefreshAttach()
DoCmd.Hourglass True
''-----------------------------
''-- Function Refresh Attach --
''-----------------------------
On Error Resume Next
Dim ldb As Database
Dim tdf As TableDef
Dim qdf As QueryDef
Dim wrktdf As String
Dim wrksrc As String
Dim r As Integer
Dim s As Integer
Dim varReturn As Variant
'' Return reference to current database.
Set ldb = CurrentDb
s = -1
Erase arrTbl

intLengte = Len(oudeDSN)

For r = 0 To ldb.TableDefs.Count - 1
'' Keep Params
If (ldb.TableDefs(r).Attributes And dbAttachedODBC) And
(Left(ldb.TableDefs(r).Connect, intLengte) = oudeDSN) Then
s = s + 1
ReDim Preserve arrTbl(0 To s)
arrTbl(s).name = ldb.TableDefs(r).name
arrTbl(s).SourceTableName = ldb.TableDefs(r).SourceTableName
End If
Next r
For r = 0 To s
varReturn = SysCmd(acSysCmdSetStatus, "Attaching Table " &
arrTbl(r).name)
ldb.TableDefs.Delete arrTbl(r).name
ldb.TableDefs.Refresh
Set tdf = ldb.CreateTableDef(arrTbl(r).name, dbAttachSavePWD)
tdf.SourceTableName = arrTbl(r).SourceTableName
tdf.Connect = "ODBC;DSN=" & iDSN & ";SRVR=" & iSRVR & ";DATABASE=" &
iDATABASE & ";UID=" & iUID & ";PWD=" & iPWD & ";TABLE=" &
arrTbl(r).SourceTableName & ""
On Error Resume Next
ldb.TableDefs.Append tdf
On Error GoTo RefreshAttach_Err
ldb.TableDefs.Refresh
Set tdf = Nothing
Next r
Erase arrTbl
s = -1
For r = 0 To ldb.QueryDefs.Count - 1
'' Keep Params
If (ldb.QueryDefs(r).Type = dbQSQLPassThrough) And
(Left(ldb.QueryDefs(r).Connect, intLengte) = oudeDSN) Then
s = s + 1
ReDim Preserve arrTbl(0 To s)
arrTbl(s).name = ldb.QueryDefs(r).name
End If
Next r

For r = 0 To s
varReturn = SysCmd(acSysCmdSetStatus, "Attaching Query " &
arrTbl(r).name)
Set qdf = ldb.QueryDefs(arrTbl(r).name)
qdf.Connect = "ODBC;DSN=" & iDSN & ";SRVR=" & iSRVR & ";DATABASE=" &
iDATABASE & ";UID=" & iUID & ";PWD=" & iPWD & ""
ldb.QueryDefs.Refresh
Set qdf = Nothing
Next r

Set ldb = Nothing

RefreshAttach_End:
varReturn = SysCmd(acSysCmdSetStatus, " ")
DoCmd.Hourglass False
Exit Function

RefreshAttach_Err:
'' Display error information.
DoCmd.Hourglass False
MsgBox "Error number " & Err.Number & ": " & Err.Description
'' Resume with statement following occurrence of error.
DoCmd.Hourglass True
Resume RefreshAttach_End:
End Function

Function AttachRemoveDbo() As Integer
On Error GoTo RemoveDbo_err
Dim CurrentDatabase As Database
Dim MyDocument As Document
Dim J As Integer
Set CurrentDatabase = DBEngine(0)(0)
For J = 0 To CurrentDatabase.Containers("Tables").Documents.Cou nt - 1
Set MyDocument = CurrentDatabase.Containers("Tables").Documents(J)
If Left(MyDocument.name, 4) = "dbo_" Then
DoCmd.Rename Mid(MyDocument.name, 5), A_TABLE, MyDocument.name
End If
Next J
AttachRemoveDbo = True
AttachRemoveDbo_exit:
Exit Function
AttachRemoveDbo_err:
MsgBox Error
AttachRemoveDbo = False
Resume AttachRemoveDbo_exit
End Function

"Cor" <no*@non.com> wrote in message
news:#5**************@TK2MSFTNGP09.phx.gbl...

Hi Pieter，

我正在写一些要求你更精确的东西，当我想要你的价格时，这可能是你想要的。

http://www.connectionstrings.com/
http://www.able-consulting.com/ADO_Conn.htm
请问我不是更精确吗？

Hi Pieter,

I was writing something to ask you to be more precise, when I thougth maybe is this what you are looking for.

http://www.connectionstrings.com/

http://www.able-consulting.com/ADO_Conn.htm

I not be something more precise please?

Cor

我想在VB.NET中创建一个重新链接所有$的小应用程序b $ b查询Access数据库中通过ODBC链接到SQL

I want to make a small application in VB.NET that relinks all the query''s and tables in an Access database that are linked via ODBC to an SQL

服务器的'和表。

Server.

它必须能够重新链接所有表和查询是给定数据库，

It must be able to relink all the tables and query''s to a given databse,

在

给定的给定的SQL服务器上ogin和密码。

Aybody知道怎么做，或者更好：有一个示例应用程序

a given sql server with given login and password.

Aybody knows how to do that, or better: has a sample application doing

这个？

" DraguVaso" < PI ********** @ hotmail.com> schrieb

"DraguVaso" <pi**********@hotmail.com> schrieb

不，它不是那样的：我需要的东西是重新连接的表和查询'，而不是连接它们。我在Access中有它，但不是在VB.NET中。 （见下文）。我只是在寻找以下代码的翻译：

No, it''s not that: what I need is something that refeshes the linked
tables and query''s, not that connects to them. I have it in Access,
but not in VB.NET. (See underneath this). I''m just looking for a
''translation'' of the code below:

我没有找到ADO.NET解决方案，但是使用ADOX ：

http ：//support.microsoft.com/default...b; en-us; 275249

-

Armin
http://www.plig.net/nnq/nquote.html
http://www.netmeister.org/news/ learn2quote.html

I didn''t find a ADO.NET solution, but one using ADOX:

http://support.microsoft.com/default...b;en-us;275249
--
Armin

http://www.plig.net/nnq/nquote.html
http://www.netmeister.org/news/learn2quote.html

这篇关于通过VB.NET在Access中重新链接（ODBC）表和查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！