-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Your connection string for a DSN-less ODBC connection is incorrect. It
should be like this.
strConnect = "ODBC;Driver=SQL Server;Server=SQL1;" & _
"Database=UK Marketing;UID=user;PWD=xyz"
Unless you are actually trying to connect thru a DSN (your original
string was ambiguous). If the connection is truly thru a DSN, then the
connection string is like this:
strConnect = "DSN=UK Marketing;UID=user;PWD=xyz"
The InitialCatalog parameter is part of an ADO connection string. The
above connection string examples are for DAO. Which are you using?
To access each database your User must be granted access to each DB. To
run an SP on each DB, the user has to have EXEC rights granted on each
SP in each DB. Do you have one DB with different schemas (owners: e.g.:
UK Marketing, France Marketing, etc.) or many DBs with one owner each?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQm6hmYechKqOuFEgEQLjBgCg6jjjkvQWCw22cm/r3V4AjCg9V/8AnjzJ
NKvZqrtWGB/rqDPNqQ648UhG
=+D8U
-----END PGP SIGNATURE-----
Shaun wrote:[color=blue]
> Hi all,
> I'm convertinmg an Access 2002 application to SQL Server with many
> differant locations, each needs their own instance of the database on
> the same physical server so I have created an INI file to provide the
> server name (i.e. SQL1) and DSN (i.e UK Marketing)
> It works fine for linking the tables BUT when I run the sprocs it will
> only work if the generic and shared user name I use has its default
> database set to the datbase I want to execute against. The connection
> string is
>
> strConnect = "Server=SQL1;DSN=UK Marketing;DRIVER=SQL
> Server;UID=User;PWD=xyz"
>
> This won't then allow me to change the DSN to say 'France Marketing'
> and keep the user the same. Do I need users for each market? Bit of a
> pain if I do as I only need it to access the sprocs on each of the
> database on the server. I did try the InitialCatalog options but that
> did not help either[/color]
