cnn.Execute - cant append data to table using sql

[stuartam]

Hi guys, i have finally managed to work out the correct connection strings needed to connect to the server, but i cant get this bit of sql too work what i want it too do is select all the data from the 'table1.tablea' table and insert it into the 'TBL-testTBL' table but when i run the code i get a values missing error pointing to the cnn.Execute line.

any ideas what i am doing wrong?

   Dim cnn As ADODB.Connection
   Set cnn = New ADODB.Connection
   Dim rs As ADODB.Recordset
   Dim vsql As String

   ' Open a connection using an ODBC.
   cnn.ConnectionString = "UID=userid;PWD=password;DRIVER={Microsoft ODBC for Oracle};" _
        & "SERVER=server1;"
   cnn.Open
    
   cnn.Execute "INSERT INTO TBL-testTBL Select * From 'table1.tablea';"
 
   ' Find out if the attempt to connect worked.
   If cnn.State = adStateOpen Then
      MsgBox "connection sucess"
   Else
      MsgBox "Sorry. can not connect."
   End If

   ' Close the connection.
   cnn.Close

best regards

stuart

 

[WayneRyan]

Stuart,

Change:

cnn.Execute "INSERT INTO TBL-testTBL Select * From 'table1.tablea';"

To:

cnn.Execute "INSERT INTO TBL-testTBL Select * From table1.tablea;"

No quotes needed. What is "table1.tablea"? Shouldn't it just be:

Select * From table1? Or TableA? Is it a table ownership thing?

Wayne

 

[stuartam]

thanks for the reply Wayne

i got the same error without the quotes

the table is just called that, there are lots of them all with the same naming standard 'name1.namea' know idea why they set it up that way, maybe its because its an oracle DB.

 

[WayneRyan]

Stuart,

All I can imagine is that it's an ownership thing - "Owner.TableName"

In SQL Server:

Select * From DatabaseName..OwnerName.TableName

In Oracle, I don't know, but just looking at the syntax --> "'table1.tablea".
There is definitely something wrong here. BOTH components seem to
reference a table entity.

Is there a table named table1 or tablea?

Wayne

 

[stuartam]

i dont know ( will look tomorrow )

but i know i can get info from 'table1.tablea'

because i told it to give me the first value from one of the columns and it worked.

maybe i need to specify the column names and which to insert where?

thanks for the help

 

[WayneRyan]

Stuart,

If the tables are dis-similar, then you need something like:

Insert Into "INSERT INTO TBL-testTBL (f1, f2, f3)
Select Fa, Fb, Fc From table1.tablea;"

Wayne

 

[stuartam]

i think i might have found out why the problem is happerning, is it because the table i am getting the data from is on an Oracle server and the table i want to insert it into is in my current project ( current access database that im running the code from )

if this is the case how do i tell it to insert it into this and not try and insert it into a table on the Oracle server.

best regards

 

[modest]

You may want to wrap the table name in brackets. I.E. [Table.Name] or [Table- Name]

See if that works before I add more =)

 

[The_Doc_Man]

I'm not going to comment on the other issues, but ORACLE creates sub-spaces that qualify tablenames. So syntax "table1.tablea" means something like "Table1" in "TableA" space (or vice-versa). To those trying to understand the funky syntax, it is just Larry Ellison being difficult by adding one more wrinkle to his product line.

If worst comes to worst and you cannot do it any other way, at least consider a loop in VBA in which you have two recordsets opened explicitly. One via ODBC to ORACLE, the other to your local DB via CurrentDB.OpenRecordset.

Now write the loop to do the move-first.

Loop:
If not oradb.EOF then
  localdb.AddNew
  localdb.Fields("A") = oradb.Fields("A")
  localdb.Fields("B") = oradb.Fields("B")
  etc.
  localdb.Update
  oradb.movenext
  goto loop
end if

Or something along those lines. Yeah, I know, it's ugly. What about Access isn't?

 

[Pat Hartman]

If you want to append Oracle data to a Jet table or vice versa, your best bet is to link the Oracle table and run an "Access" append query rather than a passthrough one.

 

[stuartam]

we fixed the problem by using a pass-through query instead of a linked table and an append or trying to get the code too work.

thanks for the replys guys