Trouble connecting to sql express

Topic

New Reply

Hello. (Access 2007)
I’ve started an ADP but am having trouble connecting to my server on my desktop pc.

I have SQL Server (SQLEXPRESS). In the configuration I can start it as Local System
or Local Service. Which do I need? Do I then need to supply a username and pasword?

SQLEXPRESS also shows an Agent and Browser. Do I need these?

Currently I can start a service but it doesn’t appear in the list of server names in the Access
Data Link Properties.

Thanks for any guidance, Andy.

Reply | Quote

Replies

SQL Agent by default is set to start manually. It is used to execute jobs, send alerts, etc. I’d just leave it alone unless you are sure you need it to start automatically.

SQL Browser is used manage client connections. I recommend you leave it alone.

You have to make sure that your firewall is configured to allow access to SQL Server.

I recommend you not use builtin accounts to start SQL Server. There is a whitepaper at Microsoft SQL Server 2008 White Paper: Security Overview for Database Administrators which explains in more detail.

Joe

--Joe

Reply | Quote

Thank you. But I just want to use SQL Server on my PC to learn about it. So I’m hoping to just connect to it from an Access adp.

If I select Local Service and it is running, why wouldn’t it appear in my list of available servers in the adp connection dialog? Andy.

Reply | Quote

Thank you. But I just want to use SQL Server on my PC to learn about it. So I’m hoping to just connect to it from an Access adp.

If I select Local Service and it is running, why wouldn’t it appear in my list of available servers in the adp connection dialog? Andy.

That is why you need to check all the security settings. What TCP ports are you using? What users have you configured the SQL Server db to allow access?

Joe

--Joe

Reply | Quote

Thanks again. Looks like I’ll have to do a load more studying before I can begin to use the server.

In Access 2003 I just used username sa and password ‘admin’ and I was up and running.. Ah well, Andy.

Reply | Quote

Are you using SQLEXPRESS 2008 or 2005.

Often you can type the name in the server box even if it is not there.
Usual notation is PCNameSQLEXPRESS if it is the default instance.

I recently had a similar issue.
I rebuilt the main pc here (after being mugged by an MS Update)
I have SQL Developer 2005 and Access 2007 under Windows 7 Ultimate.

When I tried to create an Access adp and link to existing sql server
I was getting similar connection problems.
I tried PCName, IP address, localhost
All no joy, and yet SQL Enterprise manager connected fine.

Then instead of linking to an existing sql database, I used the wizard to
create a new database. It did this ok, and after that there were no problems.

The other thing I heard reported somewhere (cannot remember where),
was that Access will only connect if SQL Browser service is running.
I have not been able to verify if this was valid though.

Sadly just another example of what a damaged effort Access 2007 is
as a database development environment (subjective opinion that one).

Reply | Quote

Andy,

I’d start with setting up and ODBC connection so that you can attach the SQL Tables to an Access MDB Database. ADPs are dissapearing in the next version of Access.

Start at. Control Panel>Administrative Tools>Data Sources.

Go to the System DSN Tab and click Add.

Select SQL Server from the list and click Finish.

Enter a name and Descritption and then

The trick, as Andrew suggests, is to enter the Server as PCNameSQLEXPRESS, (it won’t appear in the list) click next

use “With Windows NT Authentication” then Next, Next and Finish.

Test the Data Source.

This new ODBC connection is available in Access by going to Get External Data> ADBC Datasource. Select the link option and then you”ll find it on the Machine Data sources tab.

You can now select the tables you want to attach then work away as you would in any Access Database.

See how you go with that.

Reply | Quote

Andy,

ADPs are dissapearing in the next version of Access.

Are you sure about this? Where can i read up on this?

Reply | Quote

Are you sure about this? Where can i read up on this?

I don’t see any mention of ADP’s being deprecated with Access 2010 at Changes in Access 2010.

Joe

--Joe

Reply | Quote

I don’t think Projects are being removed, they are just making them
harder to find and set up like they did on 2007.
Removal of support I don’t think would be wise.
On the other hand ….

However, this article implies that adp support remains, but not sure about how much of the new SQL 2008 Features

Access 2010 Info

Reply | Quote

Pardon my Dyslexia – DAPs are being deprecated not ADPs. Sorry. Probably a bit of personal mindset. I’ve never really seen a future for ADPs (I think I got it right that time). I’ve always felt the same about Access Security and replication as well and since they are both gone I guess I expected ADPs to go with them.

I’m currently developing an Access 2010 application with SQL Server 2008 and apart from the occassional Beta Bug it’s quite nice.

SQL Server is one of those MS technologies that tries to do way too much resulting in a less than easy user experience. Finding the Server name is just one example of the frustrations new users of SQL Server will come across. I’ts one of those technologies where experience really matters.I guess this keeps DBAs in work.

Reply | Quote