Hi, I am trying to get VBA to run a select query in a netezza database and then insert that data into a table in an oracle database table, but have failed so far. Can anyone help fill in the blanks / show me what I'm missing or doing wrong please?
The connection strings work. It gets down as far as
then there's a message saying "invalid table name".
I know MY_ORACLE_TABLE is ok because I can replace the sql
with
and it will run that, so assume the problem lies with
Thanks
VBA Code:
Sub Midas_to_Oracle_table()
Dim con As ADODB.Connection
Dim ConnectionString As String
Dim recset As New ADODB.Recordset
Dim mcon As ADODB.Connection
Dim mConnectionString As String
Dim mrecset As New ADODB.Recordset
Dim SqlQry As String
Dim mSqlQry As String
Set con = New ADODB.Connection
Set recset = New ADODB.Recordset
Set mcon = New ADODB.Connection
Set mrecset = New ADODB.Recordset
mConnectionString = "dsn=NZSQL;servername=servername;port=1234;database=database;User ID=me01;password=password123"
ConnectionString = "GOODSQL.1;User ID=cheese_data;password=password456;Data Source=ORACLE"
mcon.Open mConnectionString
mSqlQry = "SELECT COLUMNS FROM TABLE WHERE ETC "
mrecset.Open mSqlQry, mcon
con.Open ConnectionString
''''SqlQry = " INSERT INTO MY_ORACLE_TABLE (SELECT * FROM " & [mrecset] & " ) "
SqlQry = " INSERT INTO MY_ORACLE_TABLE (SELECT * FROM [mrecset] ) "
recset.Open SqlQry, con
recset.Close
mcon.Close
Set mrecset = Nothing
con.Close
Set recset = Nothing
'MsgBox "I went to a Pretenders gig once. It was a tribute act"
End Sub
The connection strings work. It gets down as far as
VBA Code:
recset.Open SqlQry, con
I know MY_ORACLE_TABLE is ok because I can replace the sql
SQL:
" INSERT INTO MY_ORACLE_TABLE (SELECT * FROM " & [mrecset] & " ) "
SQL:
" select * from MY_ORACLE_TABLE "
SQL:
SELECT * FROM " & [mrecset] & " ) "
Thanks