ACCESS VBA SQL 3

[3pmilk]

Hi all,

here's my problem: When I execute my sql againast Oracle server via sql navigator, it works well. However, when executing the same query in Access, the JET engine doesn't like the Oracle reserved key words such as START WITH, CONNECT BY PRIOR, and ORDER SIBLINGS BY. Any ideas how I can convert these keywords into a language that Jet engine can parse?

Another solution I came up with which didn't work quite exactly what I want, was to write a pass-thru query in access - it worked - however I can't by pass the dialog box asking me which "machine data source" to choose from before I can execute the sql. Any ideas?

Thanks all for your help

 

[kjv1611]

I didn't think I was able to find the ADO file you mentinoed, PHV, but I just found it today, just searched for ADO*.*, the other way didn't work - something with Windows XP settings or something, I guess. I'll take a look at that. Here's a * for that reference, as I'm sure I'll need it later.

 

[MikeTruscott]

Hi everyone....I'm joining this thread a bit late but I have a question for cmmrfrds (or anyone who can answer it!).

Earlier in the thread cmmrfrds wrote this (see bottom of post) and I was wondering what the connection string would look like if I was connecting to an oracle database? The server is called "SIDESCAN" and the database I want to link to is called "miketest"

I've tried using the UDL method but it doesn't let me enter a server AND a database. I can access the database using sql

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SIDESCAN)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MIKETEST)));

....but would like to do it through vba if possible as my plan is to have an access front end on one machine whereby you can import the data to an initial Oracle database, make some changes and then transfer it over a network to the main Oracle database.

If anyone could answer this initial query or point me in the direction of somewhere I could reference...I'd really appreciate it.

Many thanks

Mike

----------------------------------------------------
Here is an example of bringing back a recordset from SQL Server. Done the same way in Oracle with a different connection string.

Dim cn As New ADODB.Connection, sql1 As String
Dim rs As New ADODB.Recordset, connString As String
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=localhost;" & _
"Persist Security Info=False"

Set cn = CurrentProject.Connection

sql1 = "select * from firsttab"
Debug.Print sql1
rs.Open sql1, cn, adOpenStatic, adLockOptimistic

Debug.Print rs.RecordCount
Debug.Print rs.EOF
Debug.Print rs.GetString

rs.Close
Set rs = Nothing

 

[PHV]

Have a look here:

http://www.connectionstrings.com/

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cmmrfrds]

If I understand you correctly, you tried the UDL wizard and cannot get it to accept the server name sidescan. If that is the case, it sounds like you do not have the Oracle client side software. There is a client file called tsnames.ora that resolves the server name to the IP address. Check with your Oracle DBA about client side software. Otherwise you could try using the IP address instead of the name sidescan.

 

[MikeTruscott]

Hello!

PHV...Thanks for the link to the website. Didn't have a chance to use it as I managed to get the UDL Wizard to work....thanks cmmrfrds! I had to add the user name and password to the connection string that was eventually produced but it works fine now.

So, as a follow on question!.....I now have two recordsets (call them rstSource & rstDestination) which are on seperate machines. I want to append the whole of rstSource onto the end or rstDestination.

What is the best way to do this? If it was sql I'd use an INSERT INTO, but because it's access I'm not sure if I can;

Use the same sql but with a DoCmd.RunSql
Use the access vba equivalent of the INSERT INTO command
Stick the whole of the source table into an array, and sequentially add new records to the destination table and copy the data in line by line.

What do you think?

Again, any help greatfully appreciated.

Regards

Mike

 

[cmmrfrds]

When you say recordsets, are these ADO recordsets in VBA code that you created from other databases or are they tables? If recordsets, is the source Access MDB's or another database such as Oracle?

 

[MikeTruscott]

Hi cmmrfrds

These are recordsets that I have created in VBA, and their source is an oracle database (which is what I needed the connection string for previously).

I'm wondering if I need to use OPENDATASOURCE?...but then I don't know what string to put in it.

Cheers

Mike

 

[cmmrfrds]

If all the data is on Oracle then use a pass through query with Oracle syntax to append the records. Do you use pass through now, if not read up on.

 

[MikeTruscott]

Morning!....

Thanks for your help....I'll have a look at pass through queries and let you know how I get on!

Cheers

Mike

 

[MikeTruscott]

Right.....I'm so close!

Basically, with the code below I get the error "Sql command not properly ended".

The connection strings, commented out below, work when I just want to view the database.....it just has a problem with INSERT INTO.

(I've tried removing the ';' after the password)

I'm looking to append the values in 'Source' to those in 'Destination'.

Any ideas?

Thanks

Mike

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Public Sub passThroughQry_II()
Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command
Dim CnxnSIDESCAN As ADODB.Connection, CnxnUCTDA1 As ADODB.Connection
Dim rstTableToEdit As ADODB.Recordset, rstTableSource As ADODB.Recordset
Dim strCnxnSIDESCAN As String, strCnxnUCTDA1 As String
Dim strSQLDestination As String, strSQLSource As String, strSQLTransfer As String, strSQLDROP As String

Set cat = New ADOX.Catalog
Set cmd = New ADODB.Command

cat.ActiveConnection = CurrentProject.Connection

Set cmd.ActiveConnection = cat.ActiveConnection

cmd.CommandText = "INSERT INTO Destination SELECT * FROM Source IN [ODBC;DSN=oracle1;database=frontend;UID=uname;PWD=pword;]"

''''cmd.CommandText = "Select * From Destination"
''''cmd.CommandText = "Select * From Source"

cmd.Properties("Jet OLEDB:ODBC Pass-Through Statement") = True

cmd.Properties("Jet OLEDBass Through Query Connect String") = "ODBC;DSN=oracle2;database=miketest;UID=uname;PWD=pword;"

''''cmd.Properties("Jet OLEDBass Through Query Connect String") = "ODBC;DSN=oracle1;database=frontend;UID=uname;PWD=pword;"

cat.Procedures.Append "MySptQuery", cmd

Set cat = Nothing
Set cmd = Nothing

End Sub

 

[cmmrfrds]

Oracle should be able to find the 2nd oracle instance (which you make the connection to) and database. So, send the SQL to 1 oracle and fully qualify the 2nd oracle name.

Something like.
Insert Into Destination
(Select * From OracleInstanceName.frontend.Source)