Setting up my users's DSN

[Cire via AccessMonster.com]

Hi all,
i'm currently using Access 2002/03 as a front-end to a back-end MS-SQL server.
Now that the application is nearing the end of development, one question
still bugs me. The situation is that my users have varying levels of access
to the shared network. Thus i can't create a File DSN, put it on a common
network shared folder and link all my pass-through queries to it.

Therefore i would like to ask how do i create a setup tool to enter the path
of a shared folder that my user's have access to and have the file dsn with
the correct settings created in that folder. The last step being setting my
pass-through queries ODBC connection strings to this created file dsn. All
this done automatically via a setup window before i place the working and
configured standalone front-end on my user's local machine. oh and i'm uisng
dao as my connection in my vba coding.

I hope the above is possible, if not that i guess i have to do it manually.

thanks

 

[Douglas J. Steele]

Any reason you're not going DSN-less?

See http://www.accessmvp.com/djsteele/DSNLessLinks.html for one approach.

 

[Cire via AccessMonster.com]

Any reason you're not going DSN-less?

See http://www.accessmvp.com/djsteele/DSNLessLinks.html for one approach.

Hi all,
i'm currently using Access 2002/03 as a front-end to a back-end MS-SQL

[quoted text clipped - 21 lines]

thanks

Hi Doug, thanks for your feedback. Actually i'm still reading up on the DSN-
less method, figuring how it works and how it would impact my code. Do u
happen to have any links or sample databases that uses DAO/ADO and dsn-less
settings to send a query to a back-end MS-SQL? because atm, AFAIK, i'm using
pass-through queries to communicate to the backend.

Thanks
Eric

 

[John Mishefske]

Any reason you're not going DSN-less?

See http://www.accessmvp.com/djsteele/DSNLessLinks.html for one approach.

Hi all,
i'm currently using Access 2002/03 as a front-end to a back-end MS-SQL

[quoted text clipped - 21 lines]

thanks

Hi Doug, thanks for your feedback. Actually i'm still reading up on the DSN-
less method, figuring how it works and how it would impact my code. Do u
happen to have any links or sample databases that uses DAO/ADO and dsn-less
settings to send a query to a back-end MS-SQL? because atm, AFAIK, i'm using
pass-through queries to communicate to the backend.

http://www.carlprothman.net/Default.aspx?tabid=81

 

[Cire via AccessMonster.com]

[quoted text clipped - 12 lines]

settings to send a query to a back-end MS-SQL? because atm, AFAIK, i'm using
pass-through queries to communicate to the backend.

http://www.carlprothman.net/Default.aspx?tabid=81

Hi John, i'm already reading up on that site but what is the difference
btw a ODBC DSN-less connection and a OLE-DB connection?

 

[Cire via AccessMonster.com]

[quoted text clipped - 3 lines]

http://www.carlprothman.net/Default.aspx?tabid=81

Hi John, i'm already reading up on that site but what is the difference
btw a ODBC DSN-less connection and a OLE-DB connection?

ok i might be wrong but it seems connection strings only apply to ADO?

 

[Cire via AccessMonster.com]

[quoted text clipped - 4 lines]

Hi John, i'm already reading up on that site but what is the difference
btw a ODBC DSN-less connection and a OLE-DB connection?

ok i might be wrong but it seems connection strings only apply to ADO?

got this from the vba help file:

----------------beginning of code -----------------
Sub ConnectionObjectX()

Dim wrkJet as Workspace
Dim dbsNorthwind As Database
Dim wrkODBC As Workspace
Dim conPubs As Connection
Dim conPubs2 As Connection
Dim conLoop As Connection
Dim prpLoop As Property

' Open Microsoft Jet Database object.
Set wrkJet = CreateWorkspace("NewJetWorkspace", _
"admin", "", dbUseJet)
Set dbsNorthwind = wrkJet.OpenDatabase("Northwind.mdb")

' Create ODBCDirect Workspace object and open Connection
' objects.
Set wrkODBC = CreateWorkspace("NewODBCWorkspace", _
"admin", "", dbUseODBC)

' Note: The DSNs referenced below must be configured to
' use Microsoft Windows NT Authentication Mode to
' authorize user access to the Microsoft SQL Server.
Set conPubs = wrkODBC.OpenConnection("Connection1", , , _
"ODBC;DATABASE=pubs;DSN=Publishers")

Set conPubs2 = wrkODBC.OpenConnection("Connection2", , _
True, "ODBC;DATABASE=pubs;DSN=Publishers")

Debug.Print "Database properties:"

----------end of code---------

It seems that an extra workspace needs to be created AND a DSN....one of my
limitations as a developer is that i don't have write access to the server
thus i guess no ADP nor ADO which was why i went down the DAO method. any
advice/feedback?

 

[Douglas J. Steele]

If you're using pass-through queries, simply set the Connect property of the
query to a DSN-less string.

If your current Connect property is, say,

"ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"

and the DSN is pointing to server MyServer, you'd replace it with

"ODBC;DRIVER={sql server};DATABASE=pubs;SERVER=MyServer;UID=sa;PWD=;"

Regarding another of your questions, you have no choice but to use an ODBC
connection string with Access.

If you're using pass-through queries, what are you doing with workspaces, as
in another of your posts?

Where are you using the DSN today?

If you have linked tables, you're probably using it there. The code on my
page will correct the connect property for each linked table with the
appropriate one, so that a DSN is no longer required there.

If you have pass-through queries, you're probably using it there, but you
can change the Connect property (either manually, or in code: with
pass-through queries, you can change the property, whereas for linked
tables, you have to drop and recreate the linked table)

If you are using a DSN in a connection string for workspaces, simply replace
what you've got with the appropriate DSN-less connection string.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Any reason you're not going DSN-less?

See http://www.accessmvp.com/djsteele/DSNLessLinks.html for one approach.

Hi all,
i'm currently using Access 2002/03 as a front-end to a back-end MS-SQL

[quoted text clipped - 21 lines]

thanks

Hi Doug, thanks for your feedback. Actually i'm still reading up on the
DSN-
less method, figuring how it works and how it would impact my code. Do u
happen to have any links or sample databases that uses DAO/ADO and
dsn-less
settings to send a query to a back-end MS-SQL? because atm, AFAIK, i'm
using
pass-through queries to communicate to the backend.

Thanks
Eric