Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Pass Through Query or Other Method?

Status
Not open for further replies.
tjherman

tjherman

Programmer
Jun 19, 2006
68
US
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???
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Try

qdf.Execute

after you've changed the SQL.

Paul
MS Access MVP 2007/2008
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Why using aliases (as col1) in a VALUES list ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Regarding PH's comments, try use:
Code: 
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
 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
Excellent! Glad we came up with a working solution for you.

Paul
MS Access MVP 2007/2008
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

combo
  • Locked
  • Question
'Wrong property setting' message in query 1
Replies
2
Views
524
combo
combo
TheresAlwaysAWay
  • Locked
  • Question
Union query provides different reslt than when summing the combined queries individually 1
Replies
12
Views
395
TheresAlwaysAWay
TheresAlwaysAWay
Moss100
  • Locked
  • Question
Append query dropping leading zero 2
Replies
4
Views
364
Andrzejek
Andrzejek
JimStrand
  • Locked
  • Question
Crosstab query - returning 1 record per week instead of 1 record
Replies
1
Views
280
JimStrand
JimStrand
JK Dutta
  • Locked
  • Question
requet for form an access query for report
Replies
2
Views
511
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top