Using a File DSN ?

  • Thread starter Thread starter Adam
  • Start date Start date
A

Adam

Hi all,

I am about to roll out my DB (Access2k front-end, SQLServer7 Backend). I
don't particularly want to set up a system dsn on all users. Is a file dsn
what I am after ?

If so, how do I get the database to use the file.dsn, and keep on using it ?
Do I need to re-attach the tables each time the user starts the database ?

any help would be greatly appreciated.

cheers,

Adam
 
You may want to look into a DSN-less connection, thus eliminating a DSN.
Another solution if you can perform the task or have the Network Admin
perform the task is to create a System DSN on the server where the SQL
database resides.
 
Thanks for the tip on DSN-Less connections.

One thing though, I'm having a bit of trouble knowing how to use the code
found on Doug Steele's website. Does anyone know how I should be using the
code ? Is it on a button, Form Load etc etc.

cheers,

Adam
 
I don't know if this constitutes a good practice, but it has worked well for
me.

I have Ac2K MDE front ends hitting Sql2K backends, using linked tables
without having to have a DSN anywhere.

All users access the back end database using Sql Security as the same user.

I have a form in the database that allows the user to link all of the tables
in the backend by providing the SqlServer name and the database name. These
fields are "seeded" in the forms OnLoad event from a local Access Table in
the MDE that holds the values from the last successful link operation, or in
the case of a new MDE values that I provide for each customer after testing.

When the user pushes OK or if the Forms OpenArgs is "AutoRun" the code:

Makes a DSNLESS Connection to SQL using Sql Security for my sole
database user.
Drops all of the Linked tables that have ODBC type connections
Creates new Links all of the appropriate tables on the server (Using the
DSNLESS Connection string).
For my apps this is typically all of the user tables. "SELECT name
FROM sysobjects WHERE type='U';"
Refreshes the SqlServer name and database name in the local table

And we are off to the races. After this code executes once all of the
tables are linked ready to be used as though they were linked by using the
Access "Get External Dat | Link Tables" interface where the user pointed to
a DSN. Even though the Username and Password is embedded in the code that
makes the DSNless Connection, it's an MDE and the code is not available to
the user making it as secure as using a DSN to link the tables.

Ron W
 
Sounds Brilliant Ron.

Thanks for the tip.

Adam

Ron Weiner said:
I don't know if this constitutes a good practice, but it has worked well for
me.

I have Ac2K MDE front ends hitting Sql2K backends, using linked tables
without having to have a DSN anywhere.

All users access the back end database using Sql Security as the same user.

I have a form in the database that allows the user to link all of the tables
in the backend by providing the SqlServer name and the database name. These
fields are "seeded" in the forms OnLoad event from a local Access Table in
the MDE that holds the values from the last successful link operation, or in
the case of a new MDE values that I provide for each customer after testing.

When the user pushes OK or if the Forms OpenArgs is "AutoRun" the code:

Makes a DSNLESS Connection to SQL using Sql Security for my sole
database user.
Drops all of the Linked tables that have ODBC type connections
Creates new Links all of the appropriate tables on the server (Using the
DSNLESS Connection string).
For my apps this is typically all of the user tables. "SELECT name
FROM sysobjects WHERE type='U';"
Refreshes the SqlServer name and database name in the local table

And we are off to the races. After this code executes once all of the
tables are linked ready to be used as though they were linked by using the
Access "Get External Dat | Link Tables" interface where the user pointed to
a DSN. Even though the Username and Password is embedded in the code that
makes the DSNless Connection, it's an MDE and the code is not available to
the user making it as secure as using a DSN to link the tables.

Ron W

Backend).
Click to expand...
 
Back
Top