Create ADP using 2007 under Vista fails

[Charles]

Under my new Vista 64 machine I can use ADP files that were created on my old
XP box. However I cannot create new ADP files on the Vista box. I get the
error message

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
denied.

I am trying to connect to a SQL 2000 Standard server.

Has anyone seen this before?

Thanks
Charles

 

[Norman Yuan]

You cannot create new ADP file or you cannot get the ADP file connected to a
SQL Server2000 instance?

 

[Charles]

It is only the create fails. If I create the ADP file on an XP box and then
transfer it to the Vista box, I can access the SQL 2000 database through the
adp file without problems.

Thanks
Charles

 

[Norman Yuan]

Let us get it clarified:

When you start a new ADP project in MS Access, after specifying file name,
you face two options: connecting to an existing SQL Server database or
creating a new database on a SQL Server. So, you are saying when you choose
the latter, you get error.

First, can you start a new ADP on the VISTA box and choose connecting to
existing SQL Server DB? if you can, then you need to verify if you (your
domain/windows user account, if you use Windows integrated security, or the
SQL Server login you use in the ADP file) have the right to create DB on
the SQL Server. Have you tried to start new ADP and create new SQL Server DB
on XP box?

 

[Charles]

Sorry if I was not clear before, I am trying to create an ADP file that
points to an existing database. It is when trying to connect to the SQL 2000
server that I get an error. The Data Link Properties dialog box opens and I
specify the server name, use Integrated security and enter the server name
and then I get the error.

However opening adp files that I created previously on my old XP box work
fine and it makes the connection to the server without problem.

Your question prompted me to try creating a new database along with a new
adp file under Vista and that worked. So it is just attaching to an existing
database that fails.

Thanks

 

[AccessVandal via AccessMonster.com]

Office button - Server - Connection - select the connection to your server
with user and password and the database- click "Test Connection" and save the
new connection. You don't need to create or import into a new ADP database.

 

[Charles]

I have tried that and for me under Vista, this fails for a new connection but
works for a connection that was setup under XP.

It sounds like this is something unique to my setup though if this is
working for other people under Vista.

Thanks
Charles

 

[AccessVandal via AccessMonster.com]

I’m not sure how you created your ADP in Access2007. But make sure in the
Access Option - Popular - creating database - default_file format is selected
to “Access 2000†or “Access 2002-2003†including the file extension from MDB
to ADP. If you did it right, a Datalink property will popup. Make sure that
you include your “PASSWORD†and “USERID†else you can’t connect. It will also
fail if you do not have a DSN.

Creation of an ADP is not supported in Access2007.

 

[Paul Shapiro]

Just to be clear, I think you mean that you can't create an adp in Access
2007 format? You can use Access 2007 to create an Access 2003-format adp. I
just did it as a test. It's not very obvious though, because the only option
I found was to click New and then it suggests it will create an .mdb. By
changing the file name to something.adp, it asked if I wanted to create a
new SQL Server db or attach to an existing db, and created the adp. I have
Access defaulted to using Access 2002-2003 format, as you suggested.

 

[AccessVandal via AccessMonster.com]

Just to be clear, I think you mean that you can't create an adp in Access
2007 format?

No, the support features for ADP are not there.

You can use Access 2007 to create an Access 2003-format adp. I
just did it as a test. It's not very obvious though, because the only option
I found was to click New and then it suggests it will create an .mdb. By
changing the file name to something.adp, it asked if I wanted to create a
new SQL Server db or attach to an existing db, and created the adp. I have
Access defaulted to using Access 2002-2003 format, as you suggested.

If the default format let’s just say is 2007, and if the user is not careful
it will not save it as a MDB but instead it’s something like

Database1.mdb.accdb

Not what you would expect.

If it were a 2000-2002-2003 format, the result would be

Database1.adp.mdb

 

[Paul Shapiro]

Is that what you see when you try this? I created an adp using Access 2007
SP1 set for creating new databases in Access 2002-2003 format, and the
database is Database1.adp. It's a real adp, not an mdb with the adp file
type. I build and develop adp's without problem in Access 2007. Sourcesafe
integration works fine. Etc.

 

[AccessVandal via AccessMonster.com]

For the sake of clarity, I can make an ADP without problems in A2007.

Yes, if you make the mistake of just changing the file extension in the file
explorer and without checking the file name after clicking the Create button.

I’m saying if the user did not change the “Save as type†to the correct
format. It’s might not be obvious to some users as you might make a mistake
of selecting the incorrect format or just simply edit the file name with the
extension without changing the file type. That’s what happen to me when
mistook it for an ADP. Even changing the File Name extension does not help if
you select the wrong format. That’s why you get “Database1.adp.mdbâ€. Changing
the file extension in Windows from MDB to ADP will only cause corruption.

In the past we had wizard, now there’s none for this.