Setup DSN-Less Oracle Link With VBA??? 1

[Pyrrhonist]

Wondering if there is a specific chunk of code that you could use to create a DSN link to an Oracle database when the Access database loads, that can then be severed on close of the database?

I've encountered the syntax as being

driver=blah;server=blah;database=blah;uid=blah;pwd=blah

but how do you put that into the VBA code??? Suggestions and assistance would be GREATLY appreciated. OH! And as a follow up query...if you create this sort of on-the-fly DSN server setting, will it add a permenant setting on the computer in the ODBC admin settings? Or will this only create an ODBC setting WHILE the application is running?

Thanks in advance for any assistance...

Shaymus

 

[cmmrfrds]

Unless you plan on joining the Oracle table to a local Access table, then there is no need to link it. Set up an ADO connection to the database and use that to interact with the database.

It would look something like this which is a connection to an sql server database.

Dim cn As New ADODB.Connection, sql1 As String
Dim rs As ADODB.Recordset, connString As String

Set rs = New ADODB.Recordset
connString = "provider=SQLO LEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=bigtuna;" & _
"Persist Security Info=False"
Set cn = connString

sql1 = "select * from dbo.Employees "
rs.Open sql1, cn, adOpenStatic, adLockOptimistic

To create the connection string for Oracle use the UDL Wizard.

Here is how you invoke the udl wizard. Do this on your desktop.

1. create a blank notepad file.
2. save the file.
3. rename the file by adding a new extention of .udl
make sure you save as all files not txt or some other
file type.
4. the icon for the file should change from notepad to a
little computer - it has now become the wizard.
5. double click on the file and it will bring up a dialog
box with multipule tabs.
6. use the Oracle provider for OLEDB.
7. look under the ALL tab and there will be parameter settings which you can change if necessary. There is a test
button, press to test the connection.
8. close the file.
9. open the file from notepad instead of double clicking the icon. You will see the connection string which you can copy and paste into your program.

 

[pgk]

Hi cmmrfrds,

How is it that you can create a blank notepad file? It asks for some data to be entered before it saves the file. After that renaming the file as a .udl and double clicking on it gives an error message.

Any clarifications would be very much welcome. Thanks.
With regards,
PGK

 

[Pyrrhonist]

Thanks for the prompt reply cmmrfrds!! That worked PERFECTLY!!! Well...one little thing that I had to change to your code...

cn.open connString

couldn't get it to work with the Set cn command you had provided.

Trouble I'm having now, and I was JUST made aware of how the Oracle DB was setup, is that there are numerous tables that are in the Oracle database, and Access has like a dozen links...one for each of the Oracle tables So is there any way to do up the sql1 string in such a way as to create a link to ALL the tables? I'll leave it to the main programmer to USE all the data in the recordset after it's been linked, but I still have to create all the links...any further suggestions are, once again, GREATLY appreciated

 

[Pyrrhonist]

Just been informed...the recordset notion of connectivity works great, for a single table, but I've gotten a peek at the Oracle backend, and there are ROUGHLY 60 tables that require linking...the avenue of solution I have been sent down now, is to create a User DSN by code, which the application will remove the registry entry upon close. Reason being that the entire Access front end relies upon the linked tables that are already created to interact with the Oracle backend. I'll post that code though (still requires some tweaking to get it working) in case anyone else following this thread is having similar concerns / problems...

DBEngine.ResisterDatabase &quot;<DNS Name>&quot;, &quot;<Driver Name>&quot;, True, &quot;<Attributes>&quot;

This will create the DSN settings in the registry, allowing the application to use the existing linked tables...

To update the links, depending on your naming conventions...

Dim tdf as TableDef

For Each tdf in CurrentDb.TableDefs
If Left(tdf.Name, 3) = &quot;tbl&quot; Then
tdf.Connect = cstrConnect
tdf.RefreshLink
End If
Next

Once again, thanks for all the assistance....sorry I was barkin' up the wrong tree, but hope that someone gets some use out of this and the other code

 

[cmmrfrds]

Sorry about the setup error. You got it right, it should have been what you put in. I had mixed it up with using the current active connection which was not your case.

You can use the connection just setup for the recordset on Forms in the application. This will only work on access 2000 and above, since the access 2000 Form has a recordset object.

If using access 97 then the record source for a Form will need to be a linked table instead of a connection to the database. I believe I have seen linking tables on startup in the FORUM. Please search the forum for &quot;linked tables&quot; or something similar.

Abbreviated example on recordset as source for Form.

Dim rs As ADODB.Recordset
rs.open
Dim yy As Access.Form
Set yy = Forms!frm_IDTable.Form '-the Form name
'-- this will make the recordset the data source for the Form.
Set yy.Recordset = rs1
'- the recordset can be used as if it was linked data. this is similar to using Me.RecordSource = &quot;my sql statement&quot; on linked tables.

 

[cmmrfrds]

pyrrhonist, we were typing at the same time. I looks like you found what you needed. What I suggested was a way to avoid linking tables altogether and just rely on an OLEDB connection to the database, but it only works on the latest versions of Access to allow the recordset as the data source.

pgk, probably what is happening is that your MDAC libraries are old and you don't have the udl code. I can save a blank notepad file on all my desktops so I am not sure of what is happening for you on that.

 

[Pyrrhonist]

Pgk....not sure how you were opening notepad to begin with, but I had to open notepad through the accessories folder, then saveas from there....hope that and cmmrfrds suggestion helps

And ya....the way you had suggested a recordset for linking Oracle to the Access DB sounded GREAT! Even looks like it would have done exactly what we were wanting (ie. no DSN connection for the user to play around with, etc.) but WAY too many linked tables already in place to be used as sources for forms (like two dozen forms already created)...guess it comes down to the superiors being lazy, and me not wanting to redesign what took them months to create **grin** Thanks again for the assistance though