DSN-Less Connection Help

[FlBrent via AccessMonster.com]

Hi,
I apologize for this, but I'm trying to create a DSN-LESS connection to
an Oracle 10g database. I want to do the connection as soon as the database
opens (at least when the switchboard fires). I've looked all over and tried
to copy code for a DSN-LESS connection but haven't been successful.
Can somebody show me some code or send me a link to some code to get me
started here.

 

[Douglas J. Steele]

I've got an example (albeit for SQL Server) at
http://www.accessmvp.com/djsteele/DSNLessLinks.html

I believe that for Oracle, you'll need to change

tdfCurrent.Connect = "ODBC;DRIVER={sql server};DATABASE=" & _
DatabaseName & ";SERVER=" & ServerName & _
";Trusted_Connection=Yes;"

to

tdfCurrent.Connect = "ODBC;DRIVER={Microsoft ODBC for Oracle};" & _
";DATABASE=" & DatabaseName & ";SERVER=" &ServerName & _
";Uid=" & myUsername & ";Pwd=" & myPassword


(which would mean passing myUsername and myPassword as parameters to the
routine)

 

[FlBrent via AccessMonster.com]

Doug,
Thanks. I'm using your code and have replaced the connect info with
Oracle driver info. However, I"m getting an error when it's trying to do the
append. The error is: Run-Time Error 3146 ODBC Call Failed. Any ideas? I
seem to be getting the linked table data just fine when looping through, but
the append is causing an issue. Thanks for any help.

 

[FlBrent via AccessMonster.com]

Doug,
Here is some of the connect code.

Set tdfCurrent = dbCurrent.CreateTableDef(typNewTables(intLoop).TableName)
tdfCurrent.Connect = "Driver={Microsoft ODBC Driver for Oracle};" & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=HP9000-DEV)(PORT=1521))" & _
"(CONNECT_DATA=(SID=SDCD)));uid=mst2;pwd=rreport;"
tdfCurrent.SourceTableName = typNewTables(intLoop).SourceTableName
dbCurrent.TableDefs.Append tdfCurrent

This is the only driver that I've used that will connect to my oracle db.
I've used it and connected via ADO and can run queries against it via vba,
but what I really need is for this tool to work, so my linked tables will
have this connection without using a DSN. I hope we can figure this out!!
Thanks.

 

[Douglas J. Steele]

Do you have a unique index defined on the table in question?

 

[FlBrent via AccessMonster.com]

Doug,
The linked tables I'm trying to update do not have unique indexes. I'm
actually using a test database that only has a few linked tables in it. None
of these are built with unique indexes. I am not choosing any key fields at
all, up front, when they are created.

 

[Douglas J. Steele]

You cannot update a table using ODBC unless it has a unique index.

 

[FlBrent via AccessMonster.com]

Doug,
The only thing I want to update is the Tabledefs. The linked tables are
all read-only, so that's fine with me. The issue I'm having is with the
append to the TAbledefs.

 

[FlBrent via AccessMonster.com]

Doug,
I hope I'm not making this more confusing than it needs to be. My
understanding was that the code did a lookup for all of the linked tables,
deleted the tabledefs for each, and added new tabledefs with the new
connection string. Is this correct or incorrect?

If it's correct, the only place I seem to be having trouble is where we're
appending the new tabledef data.

 

[Douglas J. Steele]

I'm sorry: I missed the fact that your connection string is incorrect. You
need ODBC; in front:

tdfCurrent.Connect = "ODBC;Driver={Microsoft ODBC Driver for Oracle};" &
_
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=HP9000-DEV)(PORT=1521))" & _
"(CONNECT_DATA=(SID=SDCD)));uid=mst2;pwd=rreport;"

 

[FlBrent via AccessMonster.com]

Doug,
First of all, I want to take the time to thank you. I've read so many
of your postings over the last week, it amazes me that there is somebody out
there so willing to help others. You are truly a special person and I
appreciate all that you do.

Your suggestion worked. It's funny to me, because I've probably tried 3
variations of that, slightly different of course, and they never worked. But
now it does and you've once again saved the day.

I'm married also, so if it helps any at all, please tell your wife that what
you do is greatly appreciated by tons of people that couldn't do it without
your expertise and guidance.