DSN Less Connection - 运行更新查询的问题。 [英] DSN Less Connection - problems with running update queries.

问题描述

我有以下函数来为我的链接SQL表创建DSN较少的连接;

I have the following function in place to create DSN less connections for my linked SQL tables;

函数AttachDSNLessTable（stLocalTableName As String，stRemoteTableName As String，stServer As String， stDatabase As String，可选stUsername As String，可选stPassword As String）

   

    On Error GoTo AttachDSNLessTable_Err

               


    stServer = GetSqlServerName（）

      

    Dim td As TableDef

    Dim stConnect As String

   

   对于每个td In CurrentDb.TableDefs

       如果td.Name = stLocalTableName则为
            CurrentDb.TableDefs.Delete stLocalTableName

       结束如果是
   下一个

     

   如果Len（stUsername）= 0则为
        '//如果未提供stUsername，请使用受信任的身份验证。

        stConnect =" ODBC; DRIVER = SQL Server; SERVER =" &安培; stServer&英寸; DATABASE =" &安培; stDatabase& " ;; Trusted_Connection = Yes"

   否则

        '//警告：这将使用链接的表信息保存用户名和密码。

        stConnect =" ODBC; DRIVER = SQL Server; SERVER =" &安培; stServer&英寸; DATABASE =" &安培; stDatabase&英寸; UID =" &安培; stUsername&英寸; PWD =" &安培; stPassword

   结束如果是
   设置td = CurrentDb.CreateTableDef（stLocalTableName，dbAttachSavePWD，stRemoteTableName，stConnect）

    CurrentDb.TableDefs.Append td

    AttachDSNLessTable = True

   退出功能

Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
   
    On Error GoTo AttachDSNLessTable_Err
               
    stServer = GetSqlServerName()
      
    Dim td As TableDef
    Dim stConnect As String
   
    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next
     
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function

AttachDSNLessTable_Err：

   

    AttachDSNLessTable = False

    MsgBox" AttachDSNLessTable遇到意外错误：" &安培;错误描述

AttachDSNLessTable_Err:
   
    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

结束功能

 

问题是我的更新查询不再起作用当使用这个函数时。

The problem is that my update queries no longer function when using this function.

我得到的消息是：操作必须使用可更新的查询。

可以有人请在这里看看我做错了什么???

Can anyone please see what I'm doing wrong here???

 

非常感谢！

 

 

 

 

推荐答案

Rik，

我使用的代码与您使用的代码几乎完全相同并在没有您收到的邮件的情况下运行更新查询。 我有一个问题，一次只能从Access读取表，而不是来自任何其他程序，并且它与我的其中一个表上没有
a主键有关（当我使用时它没有被转移升迁精灵）。您可能希望确保可以打开要更新的表，并在记录中手动更改表iteslf，以排除它是
a表连接问题还是更新查询的问题。

I use code that is nearly identical to what you are using and run update queries without the message you are receiving.  I had an issue once where the table was read only from Access, but not from any other program, and it was related to me not having a primary key on one of the tables (it didn't get transferred when I used the upsizing wizard). you may want to make sure that you can open the table that you are updating and make the change in the table iteslf manually on a record to rule out whether it's a table connection issue or an issue with the update query.

你的代码和我的代码之间的唯一区别是我没有"stServer = GetSqlServerName（）"  我的代码来自微软网页：

The only difference between your code and mine is that I don't have the line "stServer = GetSqlServerName()"  My code came from the microsoft web page:

http://support.microsoft.com/ kb / 892490

John

这篇关于DSN Less Connection - 运行更新查询的问题。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！