Trouble connecting ADP

[agibsonsw]

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.

[HansV]

Sorry, no experience with ADPs or SQL Server Express here...

[Charlotte]

Have you actually set up a database or merely started the service?

[agibsonsw]

Hello. I've got a new/blank .adp and am trying, via Data Link Properties, to connect to my running version of SQL Server.
Using Sql Server Configuration I am running both SQL Server and Browser as Local Service. My server appears in the server list as ANDREW-PC\SQLEXPRESS and I'm
trying to connect using NT Integrated Security. When I test the connection I receive 'Specified SQL server not found'.
Do I need to use a username and password; if so what are they - sa/admin? Do I need a database to test the connection?
Thanks for any advice, Andy.

[Charlotte]

Wish I could help, but I haven't yet reinstalled SQL Express since upgrading from Vista to Win 7, so I'm not equipped to answer.

[agibsonsw]

Hi. Perhaps someone could assist me with the following.
I'm planning to un-install all SQL Server apps and then re-install SQL Server Express 2008 (with apps). This should give me access to the (new) Management Centre and allow me to configure everything from scratch.
In my list of Add/Remove apps I have several version: SQL Server 2008, 2005 Compact Edition, Compact 3.5 and VSS Writer.
Should I un-install these in a particular order? Will removal affect any applications on my computer (particularly MS Office)?
Ta, Andy.

[Wendell]

None of those should impact MS Office, as long as you haven't created an Access application that is using either 2008 or 2005. And I would uninstall pretty much in the order you listed the apps, though other than 2008 first and then 2005, the rest shouldn't be order sensitive. Also, note that SQL Server 2008 Express (and 2005 Express) use an instance name in addition to the server name. So when you are connecting, whether with ODBC or with ADO, you need to specify both the server name and the instance name. See http://msdn.microsoft.com/en-us/library ... L.90).aspx for some background - the default instance name is sqlexpress.

As noted in that other forum, the ADP format is not scheduled for any enhancements in 2010, and none were made in 2007. I use SQL Server in virtually all of the development work I do as the back-end, but use an ODBC connection to linked tables rather than the ADP approach. Development work is quicker in general, and the capabilities using ODBC are generally greater than using ADPs. The only time I can see an ADP being preferrable is if you have major performance concerns, and at this point I haven't found a situation that would warrant using an ADP.

[agibsonsw]

Thank you. I'll propably uninstall and re-install on Saturday (or I might see if I can just install the Management Centre).
I think I've tried with both the server and instance name but I'll try again.
I turned to an ADP as I wanted to try out some SQL statements - to learn about creating tables, stored procedures etc. However, I'm also studying php and MySql and this is
proving much easier to get working.
Can Access attach to a MySql server? Thanks again, Andy.

[HansV]

Can Access attach to a MySql server?

Yes, using the ODBC driver for MySQL (MySQL :: Download Connector/ODBC)

[agibsonsw]

Thanks very much. I've almost got this working. I'm using the following ADO connectionstring to try and open a connection:
"Provider=MSDASQL; DRIVER={MySQL ODBC 3.51Driver}; SERVER= localhost; DATABASE=example; UID= Your_Username; PASSWORD=Your_Password; OPTION=3"
But it says DSN not found or no default driver found.
(There's no table in my database, I want to create one using VBA/SQL.)
Do I need a full path to my database (example)? Does it have a file extension? Thanks (anyone..) Andy.

[agibsonsw]

It's okay, I've got it working!
DRIVER={MySQL ODBC 5.1 Driver}; was required (with no provider). Perhaps the example I found was for an earlier version. Andy.

[HansV]

The link I posted was indeed for the 5.1 version, not for the 3.51 version. Glad you were able to work it out.

[agibsonsw]

I found the string from a site http://www.sqlstrings.com" onclick="window.open(this.href);return false;. Tucked way at the bottom of their home page was the year, 2004.
I wish sites would make the date more prominent. Thanks, Andy.

[agibsonsw]

Hello.
I've un-installed all SQL apps and re-installed SQL Server 2008 Express with the Management Studio. When I started this I gave 'sa' a password.
I've connected the database engine which has server name ANDREW-PC\SQLEXPRESS.
Now I've gone back to my ADP and tried to connect to this server, both using Windows Authentication and by supply username sa and the password.
But I get the same error as before - Specified SQL Server not found.

Does anyone have an idea why it can't find it? Thanks for any suggestions, Andy.

[Wendell]

Have you tried creating a new ADP using Access 2007? Also, have you tried creating an ODBC connection as either a System or File DSN? If you are running on the same PC, then you should have the SQL Server Native Driver installed, but if you are on a different PC from where SQL Server in installed, then you need to install the SQL Server Native Driver. Use the one from SQL Server 2008 - it works more reliably with either 2005 or 2008.

[agibsonsw]

Hello.
I created a new ADP to no avail. I've managed to successfully create a system DSN to connect, but the ADP data-link properties dialog doesn't have an option to use a DSN.
It requires a server name.
How can I use the DSN within ADP? Thanks for any help - this is frustrating.. Andy.

[HansV]

ADPs use a direct ADO connection, not a DSN.

[Wendell]

Hans is correct - and if you can create a DSN that works using ODBC, then you should be able to connect to and existing SQL Server database or create a new SQL Server database. Take a look at this Microsoft tutorial - if you follow the steps carefully, you should be up and running. But try creating a new SQL Server database first - an ADP uses OLE-DB to connecto to SQL Server and for some reason it seems to work more reliably if you first create a new SQL Server database, and then try connecting to an existing SQL Server database.

[agibsonsw]

Thanks, I'll give it a go. Andy.