Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: ODBC Insert on a linked table failed

    I'm getting this error message "ODBC Insert on a linked table 'Products' failed" when I try to do a DAO.recordset.Update from VBA in an Access program to a table in a back-end SQL Server database. There is no other text in the error message. The code looks like this:

    Dim db As Database
    Dim rs As Recordset

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("Products", DB_OPEN_DYNASET, dbSeeChanges)
    With rs
    .AddNew
    !ColumnInTable = UCase(NewData)
    .Update
    .Close
    End With



    I've read the Microsoft Knowledge Base article http://support.microsoft.com/default...-us;305617#top and ran the SQL commands recommended there against the SQL Server but the error persists. Is there something I can check to make sure I configured the server correctly, and the correct database, or anything else...?

    The version of Access is 2000, but may be 2002 or 2003; I can check if it makes a difference.

    Since the Microsoft article didn't help me, I don't know where else to turn...anyone???

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Does the table have a key field(s) in SQL Server, or did you specify one when linking?
    Paul

  3. #3
    Join Date
    Jul 2006
    Posts
    111
    Yes it has a key field in SQL Server.

    There are 5 columns in the table, the first is an int and is the primary key. The next is the column containing the data I try to insert, the other three allow nulls and I'm not passing it any data for these.

    I don't think any key is specified when I link...This db belongs to a client, one consultant already tried working on it, thought he had a solution but didn't, then the company I work for took it over, but I am not having much more success. The client has a workaround to run the transactions. This is the only area of their whole application that has an error. One of the issues muddling this is when I take a copy of their Access and SQL Server db's and run it on my machine, I can recreate then fix the error (using the SQL commands from the Knowledge Base article). When I implement it there, it's still broken. I can't run the linked table manager remotely; it's not installed on the server where they have me running. (But I could get that installed, if it would help).

    Please ask more questions or provide more info when you can. Thx!

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I've certainly seen that error before, but it was always due to either key problems or referential integrity problems (trying to add an invoice for a customer that didn't exist in the customer table for instance). It doesn’t sound like those are the problems, if you can fix it with that KB. I’m stumped, but will poke around.
    Paul

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    neither of these suggestions directly answers your question, but:

    particularly when you have version uncertainty, it is prudent to explicitly declare the objects:
    dim db as dao.database
    dim rs as dao.recordset


    and

    ?? drop the recordset approach (to see if the problem goes away, and to see if it is faster)?
    dim strSQL as string
    strSQL = "INSERT INTO Products(ColumnInTable) VALUES('" & UCase(NewData) & "');"
    currentdb.execute strSQL

    ...followed by a refresh of the display of the control concerned.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Jul 2006
    Posts
    111
    Yes, I was actually just thinking about rewriting the insert/update approach to see if that made a difference.

    Another recent discussin (11/2/06) on another site resolved this problem with a RefreshLink but I must not know what I'm doing because on that method I get Invalid Operation.

    DAO had been given a higher priority than ADO in the References list. THAT was to fix a Type Mismatch error!

    Thank you. I'll post back if I get a solution through my next approach.

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    good luck with it!

    from DB_OPEN_DYNASET it looks like this application has been ported from some pre-historic version of Access (1. or 2. ????)

    the current constant is dbOpenDynaset (since A95 i think).

    it is true that DB_OPEN_DYNASET and dbOpenDynaset both evaluate to 2 in my current A2K3 system.
    (Ctrl-G and try
    ? dbOpenDynaset
    ? DB_OPEN_DYNASET

    to confirm in your/clients environment)

    but once i have seen one dinosaur, i suspect that there may be others lurking around somewhere in the bushes.

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Jul 2006
    Posts
    111
    It worked!!! Then it didn't... I had a very happy customer for a very short amount of time. It's back to the same old ODBC error, but now it's on the db.Execute() of the INSERT string, like so:

    Dim strSQL As String
    strSQL = "INSERT INTO Products(BearingNomenclature) VALUES('" & UCase(NewData) & "');"
    CurrentDb.Execute strSQL

    Should something else be converted to a more modern way of doing things? You were right that there are dinosaurs lurking around. To put it in my customer's words, this application was written in the dark ages. I'd like to get my hands in as little of this code as possible because it's so old, it's large, it's someone else's and it has worked okay up until...I guess until they went from Access 97 to 2002 and then 2003 (just found out WHEN the error started was an Access version change). Should I poke around and see what else looks antiquated?

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I'd consider having a stored procedure do the insert, and simply execute it from Access, passing the value as a parameter. That way SQL Server itself is doing all the work.
    Paul

  10. #10
    Join Date
    Jul 2006
    Posts
    111
    Thanks Paul. I'll try anything. I'll let you know...

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Not sure if this would help or not but I had problems inserting records into SQL Server (in a different way though) and found a solution that I should create a new field (called anything) in the table with a data type of "timestamp". Once I did this, the problems I had went away. Apparently, it had something to do with having real or memo data type fields in the table.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    VERY good point Paul about adding the timestamp.

    and i add that the linked table should (frontend-wise) be deleted and relinked following any design change on the backend table, or you risk grief.

    izy

    LATER:

    having tried the SQL approach, you have now eliminated any potential issues to do with DAO reference, object declaration, or recordset manipulation. this is good news and bad news: you are now stuck with something stupid preventing you inserting into your table - there are only a few suspects remaining - your linked table (see above & Paul's previous), or...
    ...i don't see the 'or' yet.

    please keep us informed.
    Last edited by izyrider; 11-09-06 at 15:41.
    currently using SS 2008R2

  13. #13
    Join Date
    Jul 2006
    Posts
    111
    I wrote the stored procedure then started writing the code to call it, which I don't know how to do (not in VBA anyway, just VB.NET) but I did find the code to execute an INSERT directly through SQL Server, like so:

    Dim cnn As New ADODB.Connection
    Dim strSQL As String
    cnn.Open "ODBC;DATABASE=Inquiry;DSN=Inquiry"
    strSQL = "INSERT INTO Products(BearingNomenclature) VALUES('" & UCase(NewData) & "');"
    cnn.Execute strSQL

    And it worked on my machine (of course) but when my customer executed it she got :
    [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

    I got this same message executing on her server. It happens on the Open statement. Sorry to still be floundering, but this has something to do with authentication? Can you help me past this so I can get back to the good stuff???

    Thanks so much! You've been extremely helpful.

  14. #14
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Well, here's code executing a stored procedure (it's a lot bigger, so hopefully my cut/paste didn't miss anything relevant). I use a global connection routine, the relevant part from that is I assume the connection string (I use SQL Server security):

    Code:
    Public Const dbConnectionString As String = "Provider=SQLOLEDB;Data Source=ServerName;" & _
                                                " Database=dbName;UID=UserName;PWD=Password"
    Code:
      Dim cmd              As ADODB.Command
    
      On Error GoTo ErrorHandler
    
      Call EstablishConnection
      Set cmd = New ADODB.Command
      With cmd
        .ActiveConnection = objConn
        .CommandText = "procResAddNew"
        .CommandType = adCmdStoredProc
    
        .Parameters.Append .CreateParameter("@PassName", adVarChar, adParamInput, 25, Me.txtPassName)
    
        .Execute
    
      End With
      Call ReleaseConnection
    
    ExitHandler:
      Set cmd = Nothing
    Paul

  15. #15
    Join Date
    Jul 2006
    Posts
    111
    Thanks. A co-worker helped me with the authentication problem, now I'm timing out:

    [Microsoft][ODBC SQL Server Driver]Timeout expired

    Again, it's on the Execute of my INSERT query. So I'll be looking at that.

    I'm also thinking, this app is full of linked tables and this one is the only one having a problem. Could I just rebuild it? I know you guys mentioned adding the timestamp column, maybe that would have the same affect? It's just strange that this is the only table giving me a problem and it is not the only linked table. Now I'm not even querying it as a linked table and it's still giving me problems (direct from query analyzer, all works fine). It's a small table in terms of columns but has over 35,000 rows.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •