Cross DB query (2002 & 97)

Topic

New Reply

QUick question. Does anyone have a quick solution for doing a query incorporating information from an Oracle db and access db. All I could think of is to dump the oracle info into a temp table. If this is the only fix does anyone have a quick one for dumping a recordset to a table.

Thanks, been out of access programming for a while and trying to do a quick fix to an existing application.

Reply | Quote

Replies

Through ODBC, you should be able to link to Oracle tables, then you can work with them from Access like any table.

Reply | Quote

Through ODBC, you should be able to link to Oracle tables, then you can work with them from Access like any table.

Reply | Quote

Mark is correct, but one caution – if the Oracle table is large, Access may well try to pull the whole table in to memory to run queries – it depends on the indexing and the vagaries of the ODBC driver being used. If it does that, your queries are likely to be very slow. In that case, you may want to create an Access query to reduce the Oracle recordset size, and then join that on the Access table.

Reply | Quote

I should have stated that I wont dns-less connections. If you link it means you are required to setup the connection to the db on all PC’s before the users can use the system.

That was my reasoning behind temp tables, but I don’t know how to dump the contents of a recordset into a temp table, without stepping through each field.

Reply | Quote

I think you may be required to use ODBC connections with Access97, as ADO doesn’t really work with it. In 2002 you may be able to get by with ADO if the version of Oracle you are using is OLE DB compliant, but you will need to confirm that. If you want to avoid having to deal with those kind of issues, then I think you are going to have to create a temporary table in Access from a workstation using ODBC connections to Oracle, and then connect other users to the Access back-end. That of course means that you have static data, and all users see the same data. Hope this all makes sense.

Reply | Quote

I think you may be required to use ODBC connections with Access97, as ADO doesn’t really work with it. In 2002 you may be able to get by with ADO if the version of Oracle you are using is OLE DB compliant, but you will need to confirm that. If you want to avoid having to deal with those kind of issues, then I think you are going to have to create a temporary table in Access from a workstation using ODBC connections to Oracle, and then connect other users to the Access back-end. That of course means that you have static data, and all users see the same data. Hope this all makes sense.

Reply | Quote

I have been also working on this issue. It is slow through the ODBC. Also, depending which version of oracle and which version of Access and Windows you are running, you will need different ODBC drivers. If you have the misfortune to be running a P 4 computer with ORacle 7 or 8 there is special patch to download before the ODBC will work.

Reply | Quote

I have been also working on this issue. It is slow through the ODBC. Also, depending which version of oracle and which version of Access and Windows you are running, you will need different ODBC drivers. If you have the misfortune to be running a P 4 computer with ORacle 7 or 8 there is special patch to download before the ODBC will work.

Reply | Quote

I should add that if you use ODBC, you will have a couple of drivers to choose from – one from Oracle and One from Microsoft, and as Zave notes, there may also be different ones depending on the PC you use. The other solution to be considered would be to use the Oracle tools to dump the data to a delimited format, Excel format or whatever, and then import into Access.

Reply | Quote

All very good but doesn’t anyone know a quick way to populate a recordset into a table.

I have rstData full with the data I require and want to put into a temp table, without looping through each field. Does anyone know how to do this?

Reply | Quote

We need more information in order to try to help you. Is rstData a recordset in Access that you are manipulating in DAO or in ADO, and if it is, which version of Access are you using?

Reply | Quote

access 2002 using ado

Reply | Quote

Ah, that helps. Unfortunately with ADO you don’t have a persistent connection to the table so you can’t use simple stored queries to do the trick. But you should be able to create a SQL statement that is executed in code that in one pass selects the appropriate data from Oracle, and uses an Insert statement to create a table in Access. Are you pretty comfortable with Access SQL statements?

Reply | Quote

I have never used Access SQL statements, always Oracle or SQL Server.

How does this work. I am using an ado connection to Oracle, so querying and returning to a recordset. How do I then insert this into access. I do not want to use linked tables.

Reply | Quote

Sorry for the long delay in responding – I spent all day at a client where I don’t have Internet access

Any how, the basic syntax to create a query which does a make table would be something like:
SELECT * INTO myAccessTable From mySQLTable
where mySQLTable is an Oracle table in a database you have created an ADO connection to, and myAccessTable is the table being created. Now I have never tried this using an ADO connected table, but it seems to me it should work. Unfortunately, at the moment my SQL Server is down, so I can’t test it. It is possible it will try to create a table in the Oracle table, but I think the syntax for that would be different in Oracle so the query would fail rather than creating such a table. In any event, if you can figure out a way to do it, it should be considerably faster than steping through a recordset and creating an Access table one record at a time using VBA.

Reply | Quote

Sorry for the long delay in responding – I spent all day at a client where I don’t have Internet access

Any how, the basic syntax to create a query which does a make table would be something like:
SELECT * INTO myAccessTable From mySQLTable
where mySQLTable is an Oracle table in a database you have created an ADO connection to, and myAccessTable is the table being created. Now I have never tried this using an ADO connected table, but it seems to me it should work. Unfortunately, at the moment my SQL Server is down, so I can’t test it. It is possible it will try to create a table in the Oracle table, but I think the syntax for that would be different in Oracle so the query would fail rather than creating such a table. In any event, if you can figure out a way to do it, it should be considerably faster than steping through a recordset and creating an Access table one record at a time using VBA.

Reply | Quote

I have never used Access SQL statements, always Oracle or SQL Server.

How does this work. I am using an ado connection to Oracle, so querying and returning to a recordset. How do I then insert this into access. I do not want to use linked tables.

Reply | Quote

Ah, that helps. Unfortunately with ADO you don’t have a persistent connection to the table so you can’t use simple stored queries to do the trick. But you should be able to create a SQL statement that is executed in code that in one pass selects the appropriate data from Oracle, and uses an Insert statement to create a table in Access. Are you pretty comfortable with Access SQL statements?

Reply | Quote

access 2002 using ado

Reply | Quote

We need more information in order to try to help you. Is rstData a recordset in Access that you are manipulating in DAO or in ADO, and if it is, which version of Access are you using?

Reply | Quote

All very good but doesn’t anyone know a quick way to populate a recordset into a table.

I have rstData full with the data I require and want to put into a temp table, without looping through each field. Does anyone know how to do this?

Reply | Quote

I should add that if you use ODBC, you will have a couple of drivers to choose from – one from Oracle and One from Microsoft, and as Zave notes, there may also be different ones depending on the PC you use. The other solution to be considered would be to use the Oracle tools to dump the data to a delimited format, Excel format or whatever, and then import into Access.

Reply | Quote

I should have stated that I wont dns-less connections. If you link it means you are required to setup the connection to the db on all PC’s before the users can use the system.

That was my reasoning behind temp tables, but I don’t know how to dump the contents of a recordset into a temp table, without stepping through each field.

Reply | Quote

Mark is correct, but one caution – if the Oracle table is large, Access may well try to pull the whole table in to memory to run queries – it depends on the indexing and the vagaries of the ODBC driver being used. If it does that, your queries are likely to be very slow. In that case, you may want to create an Access query to reduce the Oracle recordset size, and then join that on the Access table.

Reply | Quote