insert statement question

[cheviac]

I am new to Oracle and in the processes of converting an Access database into Oracle. I currently have a VB6 program that opens an ADO recordset using the following SQL statement:

strSQL = "insert into foreman_data(radio_number,foreman_name,comments) select radio_" & _
' "number,foreman_name,comments from foreman_data in '" & strAccessDBPath & "'"

This statement selects data from the foreman_data table in the Access database defined in strAccessDBPath and inserts it into an identical table in a different Access database.

This works correctly but I now want to select from an Access database and insert the data into an identical table in an Oracle database. When I try to run the query I get "ORA-00933: SQL command not properly ended".

Any help with this statement will be greatly appreciated.

 

[Turkbear]

Hi,
To help debug it, use a Response.Write(strSQL)
instead of actually calling it..that way you can see exactly what is being sent to Oracle..
Post the result and we can go on..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Turkbear]

Hi again,

You may also need to use a 2 step approach..
Create a Record set from the Access data and use it to insert into the Oracle database..
Oracle may not be able to parse the in '" & strAccessDBPath & "'" part since that is not Oracle syntax..







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[cheviac]

I sorry but I am not familiar with the Response.Write. Is this for debugging purposes in VB? I did kind of suspect that the problem is the in '" & strAccessDBPath & "'" part. I know that I can select the data from Access and then loop through the result writing the data into Oracle but I was trying to minimize the coding in order to speed up the program.

 

[cheviac]

I am just going to use a 2 step approach to convert the data. Thanks for your help.

 

[ddiamond]

Cheviac,

What is your ultimate goal? Are you going to migrate your access data into to oracle and then use oracle in place of access? Is this a one time conversion effort? If so I can give you a much simpler solution.

- Dan

 

[cheviac]

I had two goals. One was to continually reload Oracle with data from an Access database that I have different versions with different test data to verify that my application is working correctly. My second goal is to eventually have a test version of the database to use in modifying my application (which I am always doing) and to use this VB conversion program to copy the current data from my production Oracle database to the test Oracle database.

I think that if I can get this conversion program working it should be easier that using imports and exports of data which seems to be more labor intensive.

 

[ddiamond]

You could move the data from access to oracle or oracle test to oracle production using nothing but access as your middle man. It would be simpler than creating a vb app which loops through each record. Simply link your oracle tables to your access database, and then create access stored queries that do a bulk insert from access table to linked oracle table or linked test oracle table to linked production oracle table. I've used both approaches in the past, and have actually found that using access stored queries out perform VB6 looping through each record.

 

[cheviac]

I will check it out and see what happens. Thanks

 

[cheviac]

Thanks, ddiamond. Its working with my old insert statements.
After I link the oracle database inside the access database I just treat the oracle tables as if they were access tables.

 

[vijip2001]

Hi guys,

I just read your solutions. I need to load my data directly to an Oracle database too. I have an ODBC created. Can you please tell me how to connect and insert data using VB?
I am new to VB. Thanks for any help.

Subha

 

[ddiamond]

Subha,

Where is your source data coming from?

- Dan