Windows – create an oracle ODBC driver without installing the Oracle Client

microsoft accessodbcoraclewindows

recently I got a task to migrate the back end of an access database to oracle (the front end should still remain in access), I need an odbc connection for that and on the PC I am working on I don't have any administrative privilages. Whenever I am trying to add an Oracle ODBC driver an error message appears stating that I need an Oracle(tm) Client + some network ingredients first. The ODBC I am choosing from the list of currently available is : Microsoft ODBC for Oracle.

Is there any way to make it work without installing any client (that probably needs to save stuff in the registry which will lead to permission being denied)?

Best Answer

Use the oracle instant client. You just need to unzip and if you want to use tnsnames.ora set the TNS_ADMIN environment variable.

Related Solutions

Sql – Copy data from Access to SQL

As well as the SSIS option, you can link directly to an access DB in SQL Server as if the database was any other linked database. You can use this to copy data accross using a task scheduled by the SQL Server Agent or some other task runner. For instance a simple "completely refresh one table from the Access DB" job might look like:

EXEC sp_addlinkedserver
   @server = 'mdb',
   @provider = 'Microsoft.Jet.OLEDB.4.0',
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = 'c:\path\to\db\db.mdb'
GO
DELETE table_to_refresh
INSERT table_to_refresh SELECT * FROM mdb...table_to_refresh_from
GO
EXEC sp_dropserver 'mdb', NULL
GO

I've used this as a quick way to copy data from an Access DB when other tools like SSIS were not available. You can use the tables (and, I assume, views) in the DB just like any other, so you are not limited to simple "select all" queries as the example above.

The 32-bit ODBC client for MS-access does not work and installing the 64-bit client is not allowed, what to do

There are two ODBC dialogs on your system. One for 32bit and one for 64bit. The one you can reach via system control is the 64bit one.

The 32bit version is located at

C:\WINDOWS\SYSTEM32\ODBCAD32.EXE (on a 32bit Windows)
C:\WINDOWS\SYSWOW64\ODBCAD32.EXE (on a 64bit Windows)

Best Answer

Related Solutions

Sql – Copy data from Access to SQL

The 32-bit ODBC client for MS-access does not work and installing the 64-bit client is not allowed, what to do

Related Topic