Problems with Access 2003 and SQL Server 2005

[atsioplakis]

Hi, everyone.
I trying to connect a SQL Server 2005 to an Access 2003 frontend. Now
in the past I have not had any trouble, but today seems to be a
different day.

Every time I go to try and open a dao.database object I get the error
"Run-Time error '3000'': Reserved error(-1038); there is no message
for this error."


I am not really sure what the issue is here. Below is the line of code



Set dbSQLServer = DBEngine.OpenDatabase("", dbDriverNoPrompt, False,
gSQLServConnectionString)


Just so everything is clear gSQLServConnectionString =
ODBC;DSN=SQLServer;Trusted_Connection=yes;DATABASE=DicomSQLServer;


Now, I have tried a simlar connection string and it works just fine for

another server. When I test the connection in the ODBC manager it
connects to the server without a problem, but when I try to get access
to
do it. I get the aforementioned error.


Thanks for all your help .


-Thanas

 

[Sylvain Lafontaine]

I don't see the purpose to add the name of the database and the trusted
connection parameter to a connection string with a DSN. Mixing these things
might confuse the ODBC driver. You should try replacing the DSN with a
DSN-less connection string and add the name of the SQL-Server driver:
Driver={SQL Server}; . See http://www.connectionstrings.com/ or
http://www.carlprothman.net/Default.aspx?tabid=81 for more informations on
this topic.

Are some other references about connection problems with SQL-Server 2005:

http://www.datamasker.com/SSE2005_NetworkCfg.htm

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

http://msdn2.microsoft.com/en-us/library/ms345318.aspx

Finally, make sure that your database is not corrupt by trying this piece of
code into a blank new database.

 

[atsioplakis]

That worked like a charm. Now I am a bit confused. I do need to have
a data source present on the computer in order to connect to a SQL
Server on another Server Correct. And when do you use DSN or what
exact is it?

thanks
-Thanas

 

[Sylvain Lafontaine]

What worked like a charm exactly?

A DSN is just an alias for a connection string that is stored in the
registry. For the ODBC driver, there is no real difference between using a
DSN or using a full connection string; however, mixing these two can give
you problems.

 

[atsioplakis]

Removing the DSN string from the connection string and putting a
Driver in its place.

Wierd.

 

[dbahooker]

you should throw away MDB and use Access Data Projects.

it is MUCH simpler than plain old normal, obsolete MDB

-Aaron

 

[atsioplakis]

I can't because we need local tables. I am eventually going to move
them complete to sql server but it going to take some time.

 

[dbahooker]

why, oh why oh why-- do you NEED local tables?

I mean seriously; please give me a big fat paragraph that says why you
NEED them.
keeping _ANY_ MDB data on the desktop is inherently insecure; it
provides poor performance; and it leads to 'multiple versions of the
truth'

keep your data in one place. build ecommerce style apps; use @@SPID as
a type of SessionID and use Access Data Projects.

10 times simpler

-Aaron

 

[atsioplakis]

I agree with you, but it is not my choice. It is just the way they do
it here. Maybe sometime in the near future I can convince/prove to
them to move to ADP/ADE or better yet VB.Net.

-Thanas