求救,ODBC链接表问题

Benjamin_luk

这是我连接用友U890 SQL服务器的方案, 你可以参考一下:
1.在SQL中创建专用的帐号(指定SQL IP)
2.在MDB数据库中使用传递查询 (要加入限制条件)
3.将查询语句用一个表存储这查询,(类型应该是"112")
4.当IP变更时, 用户先测试连接,如果要运用新的参数就将IP等资料在表中先修改,然后再用代码批量生成新的传递查询.

使用查询的最大的好处是
1.可以在查询中加入限制条件, 加快数据的读取速度.
2.不用在电脑上建立任何DSN文件
3.当IP变更时, 也可很灵活的运用.

最后发现数据的读取速度比用友前台操作的速度还快一些.
{:soso_e100:}

Benjamin_luk

这是一个生成传递查询的代码:
Public Function CreatePassSQL(SQLName As String, strSQL As String)
Dim qdfPassThrough As DAO.QueryDef, MyDB As Database
Dim strConnect As String

If IsTableQuery("", SQLName) = True Then 'doesn't exist
CurrentDb.QueryDefs.Delete SQLName
End If

Set MyDB = CurrentDb()

Set qdfPassThrough = MyDB.CreateQueryDef(SQLName)
qdfPassThrough.Connect = U890SQL
qdfPassThrough.SQL = strSQL
qdfPassThrough.ReturnsRecords = True
qdfPassThrough.Close

Application.RefreshDatabaseWindow

''DoCmd.OpenQuery SQLName, acViewNormal, acReadOnly
''DoCmd.Maximize

End Function

Function IsTableQuery(DbName As String, TName As String) As Integer
   Dim Db As Database, Found As Integer, Test As String
   Const NAME_NOT_IN_COLLECTION = 3265
   ' Assume the table or query does not exist.
   Found = False
   ' Trap for any errors.
   On Error Resume Next
   ' If the database name is empty...
   If Trim$(DbName) = "" Then
      ' ...then set Db to the current Db.
      Set Db = CurrentDb()
   Else
      ' Otherwise, set Db to the specified open database.
      Set Db = DBEngine.Workspaces(0).OpenDatabase(DbName)
      ' See if an error occurred.
      If Err Then
         MsgBox "Could not find database to open: " & DbName
         IsTableQuery = False
         Exit Function
      End If
   End If
   ' See if the name is in the Tables collection.
   Test = Db.TableDefs(TName).Name
   If Err <> NAME_NOT_IN_COLLECTION Then Found = True
   ' Reset the error variable.
   Err = 0
   ' See if the name is in the Queries collection.
   Test = Db.QueryDefs(TName$).Name
   If Err <> NAME_NOT_IN_COLLECTION Then Found = True
   Db.Close
   IsTableQuery = Found
End Function