Using DSN-Less connection to Link Tables

[ZRexRider]

Hi,

I have an application that uses MS-SQL Linked tables. I have a utility
routine that drops all links and re-establishes them. It works great
when my connection string is a DSN connection but I would like to
change it to DSN-Less so users don't have to set up ODBC connections on
each workstation.

If I use the following DSN

I'm having the same problem but linking to MS-SQL database. Would like
to use DSN-less connection to link SQL tables to MS Access Application
but always produces the following error:

"Could not find installable ISAM."

Here is the connection string that causes the error:

strConnectionString = "Driver={SQL Server};" & _
"Server=MySQLServerName;" & _
"Database=MyDatabaseName;" & _
"Uid=MyUserID;" & _
"Pwd=MyPassword"

Here is the code that links the tables:

' Loop through list of tables and link to them through ODBC source

'--------------------------------------------------------------------------
For Each varLinkedTableName In colCurrentTables
DoCmd.TransferDatabase acLink, "ODBC Database",
strConnectionString , acTable, varLinkedTableName, varLinkedTableName
Next
Note: the loop above that links these tables works perfectly if I use a
DSN connection string as seen below:

strConnectionString = "ODBC;DSN=MyDatabaseViaDSN;Description=My
Database;" 'Uid=myUserID;Pwd=myPassword"
Anybody know if there is a problem using DSN-Less connections when
executing doCmd.TransferDatabase in access?
Thanks

[MGFoster]

ZRexRider wrote:

Hi,

I have an application that uses MS-SQL Linked tables. I have a utility
routine that drops all links and re-establishes them. It works great
when my connection string is a DSN connection but I would like to
change it to DSN-Less so users don't have to set up ODBC connections on
each workstation.

If I use the following DSN

I'm having the same problem but linking to MS-SQL database. Would like
to use DSN-less connection to link SQL tables to MS Access Application
but always produces the following error:

"Could not find installable ISAM."

Here is the connection string that causes the error:

strConnectionString = "Driver={SQL Server};" & _
"Server=MySQLServerName;" & _
"Database=MyDatabaseName;" & _
"Uid=MyUserID;" & _
"Pwd=MyPassword"

Here is the code that links the tables:

' Loop through list of tables and link to them through ODBC source

'--------------------------------------------------------------------------
For Each varLinkedTableName In colCurrentTables
DoCmd.TransferDatabase acLink, "ODBC Database",
strConnectionString , acTable, varLinkedTableName, varLinkedTableName
Next
Note: the loop above that links these tables works perfectly if I use a
DSN connection string as seen below:

strConnectionString = "ODBC;DSN=MyDatabaseViaDSN;Description=My
Database;" 'Uid=myUserID;Pwd=myPassword"
Anybody know if there is a problem using DSN-Less connections when
executing doCmd.TransferDatabase in access?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your DSN-less connection string lacks "ODBC" at the beginning. It
should be:

strConnectionString = "ODBC;Driver={SQL Server};" & _
"Server=MySQLServerName;" & _
"Database=MyDatabaseName;" & _
"Uid=MyUserID;" & _
"Pwd=MyPassword"

If you want to use NT Authentication instead of SQL logins:

strConnectionString = "ODBC;Driver={SQL Server};" & _
"Server=MySQLServerName;" & _
"Database=MyDatabaseName;" & _
"Trusted_Connection=Yes"
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQh9/noechKqOuFEgEQJhLgCeNcFMxaXnX04YutHzWoNoOC/us4kAoKVr
5bKZhOH+/PloofHgB7hEDetI
=o2ps
-----END PGP SIGNATURE-----

[ZRexRider]

MGFoster - You are the MAN! (unless you're a woman)

Thanks - sometimes you just need somebody to look over your shoulder -
I completely blanked out on that.

Have a great weekend.

[Tim Marshall]

MGFoster wrote:

Your DSN-less connection string lacks "ODBC" at the beginning. It
should be:

strConnectionString = "ODBC;Driver={SQL Server};" & _
"Server=MySQLServerName;" & _
"Database=MyDatabaseName;" & _
"Uid=MyUserID;" & _
"Pwd=MyPassword"

If you want to use NT Authentication instead of SQL logins:

strConnectionString = "ODBC;Driver={SQL Server};" & _
"Server=MySQLServerName;" & _
"Database=MyDatabaseName;" & _
"Trusted_Connection=Yes"

Does anyone know how to do this with Oracle?

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[Douglas J. Steele]

"Tim Marshall" <TI****@PurplePandaChasers.Moertherium> wrote in message
news:cv**********@coranto.ucs.mun.ca...

MGFoster wrote:

Your DSN-less connection string lacks "ODBC" at the beginning. It
should be:

strConnectionString = "ODBC;Driver={SQL Server};" & _
"Server=MySQLServerName;" & _
"Database=MyDatabaseName;" & _
"Uid=MyUserID;" & _
"Pwd=MyPassword"

If you want to use NT Authentication instead of SQL logins:

strConnectionString = "ODBC;Driver={SQL Server};" & _
"Server=MySQLServerName;" & _
"Database=MyDatabaseName;" & _
"Trusted_Connection=Yes"

Does anyone know how to do this with Oracle?

Carl Prothman should have the appropriate string at
http://www.able-consulting.com/ado_conn.htm

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

[DFS]

Tim Marshall wrote:

MGFoster wrote:

Your DSN-less connection string lacks "ODBC" at the beginning. It
should be:

strConnectionString = "ODBC;Driver={SQL Server};" & _
"Server=MySQLServerName;" & _
"Database=MyDatabaseName;" & _
"Uid=MyUserID;" & _
"Pwd=MyPassword"

If you want to use NT Authentication instead of SQL logins:

strConnectionString = "ODBC;Driver={SQL Server};" & _
"Server=MySQLServerName;" & _
"Database=MyDatabaseName;" & _
"Trusted_Connection=Yes"

Does anyone know how to do this with Oracle?

Public Sub createUserLinks()

Set db = CurrentDb()
Dim connectString As String
connectString = "ODBC;DSN=DSNname;UID=userID;PWD=password"
Call createTD("table name in quotes", connectString)

End Sub

Public Sub createTD(tblName As String, connectString As String)

Set td = db.CreateTableDef(tblName)
td.SourceTableName = UCase(tblName)
td.Connect = connectString
db.TableDefs.Append td
db.TableDefs.Refresh
Set td = Nothing

End Sub

[Tim Marshall]

DFS wrote:

Does anyone know how to do this with Oracle?

Public Sub createUserLinks()

Set db = CurrentDb()
Dim connectString As String
connectString = "ODBC;DSN=DSNname;UID=userID;PWD=password"
Call createTD("table name in quotes", connectString)

Thanks D, but that's DAO stuff I already do (except I apply it to PTQs
as opposed to linked tables) and it still relies on an existing DSN. 8(

One of the great unfulfilled hopes I have with A2003 when I switched
from A97 was to be able to set up DSNless connections using ADO. SInce
ADO doesn't have the ability to produce persistent pass through
querydefs (which I frequently use for list boxes, combos, form
recordsources, etc) I've given up on it and have stuck with DAO.

BTW, I was reading your notes on A2003 corrupting when the FE gets large
- that happens to me a lot. I find A2003 hideously flakey compared to
A97, but my users have such difficult times with A97 apps when they use
A97 on XP boxes with components from Office 20003 installed as well,
I've given up on A97 and am trying to make do with A2003.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[DFS]

Tim Marshall wrote:

DFS wrote:

Does anyone know how to do this with Oracle?

Public Sub createUserLinks()

Set db = CurrentDb()
Dim connectString As String
connectString = "ODBC;DSN=DSNname;UID=userID;PWD=password"
Call createTD("table name in quotes", connectString)

Thanks D, but that's DAO stuff I already do (except I apply it to PTQs
as opposed to linked tables) and it still relies on an existing DSN.
8(

Whoops. I see I didn't read the question before tossing out a solution that
didn't fit.

One of the great unfulfilled hopes I have with A2003 when I switched
from A97 was to be able to set up DSNless connections using ADO.
What are their advantages? I'll look into them, though I haven't done much
with the ADO libraries.

SInce ADO doesn't have the ability to produce persistent pass through
querydefs (which I frequently use for list boxes, combos, form
recordsources, etc) I've given up on it and have stuck with DAO.

BTW, I was reading your notes on A2003 corrupting when the FE gets
large - that happens to me a lot.
It makes me furious, because I feel like I have to make a backup every 30
minutes. And MS asks if I want to send an error report - well, I've sent
about 100 of them and still haven't seen any Access patches.

It just happened again yesterday. I decompiled and brought it back from the
dead. Now I'm just waiting for the next time. I will say no user of mine
has ever reported the .mde corrupting, so that's good.

Here's a typical app crash log entry for module corruption (from Event
Viewer): Faulting application msaccess.exe, version 11.0.6355.0, stamp
40aa97a8, faulting module vbe6.dll, version 6.4.99.72, stamp 40b29ba6,
debug? 0, fault address 0x0008baa8.

Is there a later vbe6.dll available?
I find A2003 hideously flakey
compared to A97, but my users have such difficult times with A97 apps
when they use A97 on XP boxes with components from Office 20003
installed as well, I've given up on A97 and am trying to make do with
A2003.

I don't have problems with the tables, ever. Only the modules, and only
when they get large. Or the problem might be lots of forms. I try to make
the forms as light as possible, so rather than attach the code there the
bulk is in modules. But nothing seems to help, least of all Nishant from
Hyderabad India, at su*****@microsoft.com

[Br]

DFS wrote:

Tim Marshall wrote:

DFS wrote:

Does anyone know how to do this with Oracle?

Public Sub createUserLinks()

Set db = CurrentDb()
Dim connectString As String
connectString = "ODBC;DSN=DSNname;UID=userID;PWD=password"
Call createTD("table name in quotes", connectString)

Thanks D, but that's DAO stuff I already do (except I apply it to
PTQs as opposed to linked tables) and it still relies on an existing
DSN. 8(

Whoops. I see I didn't read the question before tossing out a
solution that didn't fit.

One of the great unfulfilled hopes I have with A2003 when I switched
from A97 was to be able to set up DSNless connections using ADO.

What are their advantages? I'll look into them, though I haven't
done much with the ADO libraries.

SInce ADO doesn't have the ability to produce persistent pass through
querydefs (which I frequently use for list boxes, combos, form
recordsources, etc) I've given up on it and have stuck with DAO.

BTW, I was reading your notes on A2003 corrupting when the FE gets
large - that happens to me a lot.

It makes me furious, because I feel like I have to make a backup
every 30 minutes. And MS asks if I want to send an error report -
well, I've sent about 100 of them and still haven't seen any Access
patches.

It just happened again yesterday. I decompiled and brought it back
from the dead. Now I'm just waiting for the next time. I will say
no user of mine has ever reported the .mde corrupting, so that's good.

Here's a typical app crash log entry for module corruption (from Event
Viewer): Faulting application msaccess.exe, version 11.0.6355.0, stamp
40aa97a8, faulting module vbe6.dll, version 6.4.99.72, stamp 40b29ba6,
debug? 0, fault address 0x0008baa8.

Is there a later vbe6.dll available?

I was getting that with my ADP that is backed onto an SQL server. It
appeared to be some form of corruption in the modules. It also seemed to
only hapen when I edited my A2000 ADP in A2003...

Br@dley