steps to convert mdb to adp ??

[Guest]

what are the exact steps to create a mdb to adp ? I have a mdb -
database/screens,etc. I want to create an adp. I have seen the database
utilites - upsizing and make mde file. The documentation isn't helping much.
I am so confused.

I want to convert my database to sql server and I want my mdb project
converted to adp. I have tried to create the adp project first from existing
database, but it has nothing in it. I need help to understand the order of
the steps before I do this.

 

[Albert D. Kallal]

Are you sure you want to do this?

If you need a ms-access application to work with sql sever, then if from day
1 you created a adp, and worked forward, then a adp product makes sense.

However, if you have an *existing* application, then likely you best leave
it as is, and simply used linked tables to sql server. The reason for this
is to keep exiting code. If you have dao code in your existing mdb, then you
will have to throw it out when you up-convert to a adp project.

If you use linked tables to sql server, then you don't have to re-write your
existing code. In fact, about 99% of your code will work as is if you use
linked tables.

in fact, if you plan to write an application that works with sql server,
*MOST* ms-access developers actually stick with using a mdb, and linked
tables to sql server. Further if you now have linked tables to your back
end, then you likely already had extensive experience with linked tables.
(the leap to sql server for the data is thus not that large if you have a
split database now).

So, I not really really sure if you want to convert to a adp? It really
depends on how much existing code the current application has. I would
strongly suggest that you consider sticking with a mdb, and simply link your
tables to sql server. (however, keeping your mdb, or moving to a adp will
STILL requite that you have a good comfort level with sql server...do you?).

If you don't have a good comfort level with sql server, this ms-access group
might be the wrong group to be hanging about....

 

[Guest]

Hi Albert,
To tell you the whole story- it was converted to an adp. I am just trying to
remember the 'how' of it, since I cannot get it working as of today.
I inherited this from somebody else, and it was not my idea to convert it.
So I am stuck with it.
Right now, I think my main problem is that I am getting all kinds of sql
server errors I don't understand.

Can you answer me this: Why does my sql server say it's running ( green
arrow on icon ) but when I try to use upsize wizard ( on a simple test mdb
that I created ) it says that I can't connect and gives me all kinds of
errors? Horrors! Does this mean I have to reinstall sql server? I know im
jumping all over the place, but nothing is working right.
Thanks for your input.

 

[Van T. Dinh]

Microsoft used to recommend ADP but more recently, Microsoft advised that
the preferred format is MDB / MDE in this case. Thus, I would be very
reluctant to convert my databases to ADP format.

Can you set up an ODBC DSN to an existing database on the SQL Server, use
the DSN to create linked Tables and retrieve data from your PC?

Do you have Create Database permission on the SQL Server?

 

[Guest]

I can go into the .mdf on my pc through sql server, but I can't create an adp
file - from mdb again. That's my problem. If I cannot do this, then maybe I
should try reinstalling sql server.What does this error message mean coming
from sql server:
"The upsizing wizard only workds with sql server (v 6.5 sp5 or higher)
Please log into a sql server data source"..... Maybe if I knew what this
meant, I could solve my problem?

thanks

 

[Van T. Dinh]

I am not sure what you described:

* MDF is a file solely managed by SQL server, not something that users
access. Users normally access the data through server\database\etc ..., not
the MDF file.

* The error message you got sounds like an Access error, not SQL Server
error. It referring to the Access Upsizing Wizard having problems creating
the SQL database.

* Which version of SQL Server are you using? The latest version is SQL
Server 2005 but there are probably more SQL Server 2000 out in the fields.
The previous version is SQL Server 7 and the earlier one is SQL Server 6.5
but I don't expect to see many as main-stream support from Microsoft has
stopped on SQL Server 7 and 6.5, AFAIK.

 

[Guest]

Hi Van,

I was just trying to explain that I can access databases on sql server. The
users do not access the .mdf file. They access it through the adp(ade) that
is on each of their desktops. I just wanted to recreate the steps I took in
order to get to an adp project for documentation purposes. Then I ran into
this problem. I am using sql server 2000. It's just strange because I did
this before without any problem and I haven't changed anything.

I appreciate your input. Thanks.

 

[Van T. Dinh]

Let's confirm a few things:

1. Which version of SQL Server 2000 you are referring to (Standard /
Enterprise om a server box or the Developer's version on your PC)?

2. Have you checked out which protocols the SQL server listens on (Named
Pipe, TCP/IP, ....)?

3. Which security models have been implemented on the SQL Server (Windows
Pass-Through Authentication or SQL (Server) Authentication or both)?

4. Do you have SQL Server Administrator's permission or at least Create
Database permission?

5. Which Access version are you using?

6. Has your computer been patched up-to-date? Server?

I have done about 4-5 Upsizing to SQL Server back-end (Standard version and
Developer's version) from Access 2000, 2002, 2004 and I simply followed the
steps in the dialogs without any problem.

 

[Guest]

1. Im using sql server 2000- developer addition on my PC
2. Im using TCP/IP
3. WIndows Authentication
4. Yes, I can create databases.
5. I am using Access 2003 SP 1
6. I am not sure if it is up to date with everything. I downloaded the most
recent sql server 2000 back in June.

 

[Van T. Dinh]

See if the following Microsft Knowledge Base article helps:

http://support.microsoft.com/kb/838594/en-us

 

[Guest]

Van,

Thanks. I will check out the article. I was able to solve the problem You
had mentioned checking tcp/ip. I also got another email from Peter Yang where
he explained how it seemed that sql server was listening only on shared
memory.
The TCP/IP and named pipes were not enabled. They must've been disabled at
some point. Thanks again.

 

[Van T. Dinh]

You're welcome ... Good lucks with your project....

Note that Microsoft currently recommends the use of linked Tables (i.e. MDB
format) as the preferred method of using Access Front-End / SQL Server
Back-End ...