Pass Through Query or Other Method?

[tjherman]

I'm trying to insert data into a Sybase table (set up with an ODBC connection). I can insert data just fine with a pass-through query that I've set up if I manually enter the values for the fields. However, I need to pull the values from a local Access table to insert into the Sybase table. When I try to run it this way, I get an error message stating: "ODBC -- call failed. Sybase ODBC Driver Adaptive Server Anywhere - Table 'tmpInsertContactsA' not found.

It makes it sound as if I can only refer to sybase tables within the query and not any of the local tables or queries. Is there a way to create a pass-through query where I can use local tables and insert into the ODBC tables?

I had originally linked the Sybase table with an ODBC connection -- the problem with that method is that when the table is linked, it converts the autonumber field to a number field -- which prohibits me from running an append query against it because it complains about null/duplicated key values (because it no longer autonumbers the key). So I was trying to find a work-around and found the pass-through which does preserve the autonumber aspect but isn't allowing me to reference a local Access table.

Any suggestions on how to handle this???

 

[pbaldy]

This is a shot in the dark, as I haven't worked with Sybase, but in SQL Server I can set up a "linked server". That would usually be another server, but it can be an Access database. If Sybase has similar capability, it would enable you to refer to the table in the Access db from within Sybase.

Paul
MS Access MVP 2007/2008

 

[dhookom]

A possible kludge would be to create a recordset of the Access table. Then loop through the table record by record and execute pass-through queries to append records. You could use one pass-through and just change its SQL property for each Access record.

Duane
Hook'D on Access
MS Access MVP

 

[tjherman]

Thank you both. I'm not seeing a way to link my Access database with a linked-server type option in Sybase (although Sybase certainly isn't my specialty).

I may work on the suggestion of looping through an Access-based recordset and running a pass-through query with each loop.

Thank you both for responding!

 

[tjherman]

Does anyone know how to actually run a pass-through query with VBA? I've got code that I've modified that should update the SQL string, but I'm not seeing how you actually execute the query? Here's what I've got:

Dim sSQL As String
Dim dbs As Database
Dim qdf As QueryDef

MyStr7 = "Select * from tmpInsertContactsA order by Lastname"
Set MyDb7 = CurrentDb
Set Myset7 = MyDb7.OpenRecordset(MyStr7)

If Not Myset7.EOF Then
Myset7.MoveFirst
Do Until Myset7.EOF
Myset7.Edit
sSQL = "INSERT INTO CONTACTS ( Title, LAST_NAME, FIRST_NAME, ADDRESS_NAME, EMAIL_ADDRESS, Availability, Contact_Type_Ref )" _
& " '" & Myset7!Title & "' as Col1, '" & Myset7!LastName & "' as Col2, '" & Myset7!FirstName & "' as Col3, '" & Myset7!Name & "' as Col4, " _
& " '" & Myset7!EmailAddress & "' as Col5, 'A' as Col6, 26 as col7"
MsgBox sSQL
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryPassThrough1")
With qdf
.SQL = sSQL
.Close
End With



qdf.Close
Set qdf = Nothing
Set dbs = Nothing
Myset7.MoveNext
Loop
Myset7.Close
End If

 

[pbaldy]

Try

qdf.Execute

after you've changed the SQL.

Paul
MS Access MVP 2007/2008

 

[tjherman]

When I add this, I get an error message that says "Runtime Error 3065: Cannot execute a select query.

Here's what I have:
If Not Myset7.EOF Then
Myset7.MoveFirst
Do Until Myset7.EOF
Myset7.Edit
sSQL = "INSERT INTO CONTACTS ( Title, LAST_NAME, FIRST_NAME, ADDRESS_NAME, EMAIL_ADDRESS, Availability, Contact_Type_Ref ) VALUES (" _
& "'" & Myset7!Title & "' as Col1, '" & Myset7!LastName & "' as Col2, '" & Myset7!FirstName & "' as Col3, '" & Myset7!Name & "' as Col4, " _
& " '" & Myset7!EmailAddress & "' as Col5, 'A' as Col6, 26 as col7)"
'MsgBox sSQL
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryPassThrough1")

With qdf
.SQL = sSQL
.Execute
.Close
End With

Set qdf = Nothing
Set dbs = Nothing
Myset7.MoveNext
Loop
Myset7.Close
End If

 

[pbaldy]

Hmm. I typically only use pass-through queries for reports; I execute stored procedures for inserts and such. Try:

DoCmd.OpenQuery "qryPassThrough1"


Paul
MS Access MVP 2007/2008

 

[PHV]

Why using aliases (as col1) in a VALUES list ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Regarding PH's comments, try use:

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryPassThrough1")

If Not Myset7.EOF Then
    Myset7.MoveFirst
    Do Until Myset7.EOF
        sSQL = "INSERT INTO CONTACTS ( Title, LAST_NAME, FIRST_NAME, ADDRESS_NAME, " & _
               "EMAIL_ADDRESS, Availability, Contact_Type_Ref ) VALUES ('" & _
                Myset7!Title & "', '" & Myset7!LastName & "', '" & Myset7!FirstName & _
                "', '" & Myset7!Name & "', '" & Myset7!EmailAddress & "', 'A' , 26)"

        'MsgBox sSQL
        
        With qdf
            .SQL = sSQL
            .Execute
        End With
      Myset7.MoveNext
    Loop
    Myset7.Close
End If

Note I moved some lines around and deleted others.

Duane
Hook'D on Access
MS Access MVP

 

[tjherman]

Success! I used dhookum's restructuring but it still failed on the Execute with the same error "Cannot execute a select query." So I commented out just the Execute line and then added pbaldy's OpenQuery and it now loops through and adds my records!

Thank you both so much for your help!

 

[pbaldy]

Excellent! Glad we came up with a working solution for you.

Paul
MS Access MVP 2007/2008

 

[dhookom]

It could be an issue with the "Returns Records" property of the p-t. However, if it is now working, I would immediately stop all testing and "wondering about"

Duane
Hook'D on Access
MS Access MVP