Access 2007 adp and SQL Server Express

[miwebster]

Hopefully, I'm missing something obvious. But I can't connect to my
SQL Server Express Instance with an adp.

I set up the connection in Data Link Properties, and it tests
successfully. But at the top of the window it always says "database
name" (disconnected) Project (Access 2002-2003 file format).

But none of the tables, views or SPs show up. I would greatly
appreciate any help. I'm finding the Vista/2007 combo very
frustrating so far.

Thanks,
Mike

 

[Sylvain Lafontaine]

In the connection properties, you must select a database (item 3). ADP
cannot work directly against the sql-server, only against a selected
database.

 

[miwebster]

Thanks for the reply. I am doing that, actually I've tried a number
of different databases. The connection always tests successfully, but
I don't see any of the DB schema. And I have the note at the top of
the screen stating I'm disconnected from whatever DB I select.

Mike

 

[Sylvain Lafontaine]

Hum, is this a dynamically attached database file?

Also, any particularity for the security and/or the schemas? Are you using
dbo. everywhere or if you have used another user or schema than dbo?

Also, what happens is you try to create a new ADP project with the creation
of a new database (instead of using an existing one) on the server?

 

[Sylvain Lafontaine]

Also, are you clicking on the OK button at the bottom of the dialog window
or if you are simply closing it by clicking on the X at the upper right
corner?

 

[miwebster]

Sylvain,

Thank you. That worked wonderfully. When I created a new database it
worked just fine, then I just changed to the db I needed, and it
connected without a hitch.

To answer the other question, I am using dbo everywhere.

I greatly appreciate the assistance.

Mike

 

[Sylvain Lafontaine]

If you need to create a database before being able to connect to the other
one, I'm not sure if your setup is really OK. What's the exact name of the
server are you using (example: « . », « .\SQLExpress2005 », « (local) »,
« (local)\SQLExpress2005 », « localhost », « localhost\SQLExpress2005 »,
etc.) ?

If I were you, I would go for TCP/IP and I would make sure to use TCP/IP and
not the named pipes protocol or the multiprotocol, even on the local
machine. Using a TCP/IP address for the server, creating an alias (under
the
tab SQL Native Client Configuration / Alias in the SQL Server Configuration
Manager program) or adding the prefix tcp: are all good ways of making sure
that you are using tcp/ip and not the named pipes protocol (np: ). Adding
the port number (usually 1433 if you have a single instance but not
necessarily 1433 if you have multiple instances) might be also a good idea.

When configuring the server with the SQL Server Configuration Manager, don't
confuse the Server Network Configuration Protocols with the Client
Configuration Protocols. I'm not sure but I would think that the SQL Server
Browser that you will find in the SQL Server 2005 Surface Area Configuration
utility must also be activated and started if you don't want to have
problem; especially if you have multiple instances and don't want to mention
the port to be used.

If the server is a remote server (ie, it's not the local machine), then the
TCP/IP protocol must be enabled in the Remote Connection tab for the
Database Engine in the SQL Server 2005 Surface Area Configuration utility.
If you have a problem and don't know why then check the Name Pipes protocol,
too.

Here are some good articles on resolving connection problem with SQL2500
Express:

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

http://blogs.msdn.com/sql_protocols...e-the-target-machine-actively-refused-it.aspx

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

http://www.connectionstrings.com/?carrier=sqlserver (connection strings for
sql providers)
http://www.carlprothman.net/Default.aspx?tabid=81 (idem)

 

[nutter]

In the connection properties, you must select a database (item 3). ADP
cannot work directly against the sql-server, only against a selected
database.