SQL Tables

[Guest]

I'm trying to convert a complex .MDB database into .ADP. This is all new to
me so I'm learning. I have read here that ADp files don't have data. If
this is so, where is the data? Currently all my data resides on an MDB file
(I long ago split the code from the data). What do I have to do to get those
tables onto the SQL server so my MDP front end can see/connect to them?
Please be very clear with your answer as this is all very new to me.

Thanks,

 

[Graham R Seach]

You're familiar with the concept of splitting the database. Well, an ADP is
the same kind of architecture, except the backend is SQL Server, instead of
Access. In an ADP, the tables (and data), and views (queries) are in SQL
Server. You will also have access to stored procedures (which are not
present in Access), and database diagrams (although Access offers a single
relationships diagram, SQL Server allows you to create many more of them).

To get your data into SQL Server, it's probably easiest to import your
backend tables to your frontend, then use the Upsizing Wizard (Tools |
Database Utilities | Upsizing Wizard) to get them into SQL Server.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Guest]

Graham, Thank you for your response. So, let see if I understand. If I
import the tables back into my front end database, then upsize to the MDB
file, the tables and queries (which become stored procedures) actually reside
on the server. Ok, so if I distribute a copy of the MDB, I'm not really
distributing tables and queries because they reside on the sql server,
correct? Also, if I distribute the MDB how can I make sure it automatically
connects to the SQL database?

Thanks again for your help.

Roy

 

[Robert Morley]

By "MDB", I assume you meant "ADP" in the last two cases?

In any event, ADP's by their nature will try to connect to the SQL Server
when they open, or will give you an error if they can't. You simply specify
the connection information once (in File, Connection...or in the upsizing
wizard), then distribute the ADP; no re-linking or anything else is
required.

Maybe it would help you to get a better idea of what's going on to simply
create an ADP from scratch and then connect to something like the SQL Server
version of the Northwind database. You'll see all the Northwind tables and
views immediately, but you'll have no forms, reports, etc. If you look at
the size of your newly created ADP file, you'll see that it's only a few
kilobytes, because there's really not much more than the connection
information being stored in it.



Rob

 

[Graham R Seach]

Roy,

<<...which become stored procedures.>>
No. Queries do not "become" stored procedures. The Queries remain queries,
albeit transferred to SQL Server. In SQL Server, a query is called a "view".
Stored procedures are collections of SQL statements, much like a VBA
procedure, except they're in SQL. If you want stored procedures, you'll have
to create them yourself.

<<...I'm not really distributing tables and queries...>>
Correct.

<<...how can I make sure it automatically connects...>>
Robert has already answered that.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

 

[Guest]

Graham and Rob, thanks again for your replies. One more question, will each
user who gets a copy of the ADE file need to have an ODBC connection
established to the SQL server?

Thanks,

 

[Robert Morley]

To my knowledge, Access takes care of everything. Certainly I've never had
any problems simply distributing an ADP to my users without having to do
anything special on their machines.



Rob

 

[Graham R Seach]

Roy,

No, and ADPs/ADEs use OLE/DB, not ODBC.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

 

[Guest]

Once again, Graham and Rob, thanks so much for your help. Is there a
reference that explains this step-by-step for we novices to the ADP/SQL world?

Thanks, again.

 

[Norman Yuan]

ADP/ADP does not use ODBC connection, while *.MDB/*.MDE front end does.

 

[Graham R Seach]

Roy,

I don't know of anything that explains it step-by-step. That doesn't mean
such a resource doesn't exist - just that I don't know of it. Access Help
should give you enough information about ADPs/ADEs. Jeff Conrad MVP has a
list of good books you might consider buying:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#Books

As for the SQL Server end of the deal, I have a truckload of books that have
been of great help to me. The first of which can be found at:
http://www.microsoft.com/mspress/easterneurope/books/book16824.htm

The complete list of Microsoft Press books can be found at:
http://www.microsoft.com/mspress/easterneurope/subjects/subjectha_p1.htm

The SQL Server training material found at the following site are really
excellent (I refer to them often):
http://www.microsoft.com/mspress/easterneurope/Pages/MCDBA_416.htm

I know these are all from Microsoft's Eastern European site, but it was the
first site Google returned. It's enough to give you titles and ISBNs.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------