ADP Access 2003 bug?

[lynn_victoria]

Work: WinXP Pro/ Office 2000/ Access 2000
Home: WinXP Pro/ Office 2003/ Access 2003

When I open a .adp file at home in Access 2003 I get the Access GUI(querys,
forms) instead of the SQL Server GUI (views, stored procedures.)

When I open a .adp file at work in Access 200 I get the SQL Server
GUI(views, stored procedures.)

I need to be able to work on my db's at both home and work. What is the
problem?

 

[Arvin Meyer]

SQL-Server databases are not really portable like JET databases. You can do
a back-up at work and restore at home (then visa-versa) if you have admin
privileges at both places, and SQL is set up on both machines.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[lynn_victoria]

Yes I know that; SQL is set up on both machines. I know how to detach and
attach a db; which i do all the time.
That is not the prob; the problem is with the GUI of Access 2003 as I
described. Please address the question of that issue. Has anyone else seen
this?

 

[lynn_victoria]

I thought I would provide the version # Microsoft Office Access
2003(11.5614.5703)

I double checked the Adminstrator priviledges and I am Administrator on the
home machine as well as work.
I have SQL installed and have used Enterprise Manager and Query Analyzer to
attach and detach my db. Unless there is something up with this Version of
Access 2003 what can it be?

 

[Sylvain Lafontaine]

Not really sure about your description of the problem and what you really
mean by Access GUI and SQL-Server GUI : ADP *is* an Acess GUI and will
display Forms, Queries, Reports, Pages (DAP) and Modules. The only
difference with MDB is the source of the Queries: SQL-Server instead of a
MDB file.

Suggestion: try to create a blank new ADP file and then connect with it to
your local server at home.

Make sure also that you are not using a Windows' shortcut that would point
to a MDB file instead of an ADP file.

S. L.

 

[victoria]

When you open an Access 2000 .adp file your db container is of the SQL Server
flavor. The choices in the left menu are:
Tables, Views, Database Diagrams, Stored Procedures, etc
If you open an Access .mdb file the db container is of the Access flavor and
the choices on the left are Tables, Querys, Forms, Reports, etc.

I DO know how to connect a db and I am not connecting to the improper type.
If what you say is the case, where in Access 2003 do you write a Stored
Procedure? In an Accesss 2000 .adp you choose Stored Procedure, New, and
choose Create Procedure in designer. Am I missing something in 2003? Where
is the Stored Procedure?

 

[Norman Yuan]

I guess this is what you are doing: you copy the same *adp file back and
forth between work and home, right?

There is nothing wrong with the Access version, nor the *.adp file. The
*.adp file remembers the connection to the backend SQL Server/MSDE when you
close it. When you carry to *.adp file home and opens it, it will try to
connect to the SQL Server as it remembers, of course the connection cannot
be established; when the connecting attempt is timed out (you must have
noticed the *.adp project takes longer time to open), Access continues on to
open the *.adp file. So, you only see everything in the *.adp file (forms,
reports, modules), you don't see tables, views, SPs, 'cause they are in the
SQL Server, which cannot be found, as the *.adp file remembers. You need to
click menu "File->Connection..." to get this *.adp project to connect to the
available SQL Server (on your home computer). When you carry the *.adp file
back to office, you need to do it again.

It is not a big deal if you just occasionally carry some work home. If you
really want to have the ADP project automatically find the SQL Server, you
could write some code on its startup to set CurrentProjecy.Connection to
different SQL Server by using
CurrentProject.OpenConnection()/CloseConnection() methods. Say, you can test
computer name to deside which SQL Server is available on this computer...

HTH

 

[Sylvain Lafontaine]

With ADP 2003, Views, Stored Procedures and User Defined Functions (UDF) are
all displayed in the same tab: Queries. Choose Queries and then New to have
a choice of creating a new view, function or Stored Procedure, with either
the Design Mode or the Text Mode.

I don't remember exactly for ADP 2000 but I don't remember any change at
this level between ADP 2000, 2002 or 2003 (I have used all three of them).
As far as I remember, the GUI interface for ADP has never been identical to
the GUI for the SQL-Server Enterprise Manager and the Views, Functions and
Stored Procedures have always been displayed in the same tab (or window) but
I may be wrong.

S. L.

 

[victoria]

Thanks Sylvain, I think that is the answer! I will check when I get home and
let you know for sure. I got to thinking it must be different in 2003 and
just didnt know where to look. Thank you.

 

[victoria]

I guess you didnt read all of the posts, but no problem. I do not have a
problem connecting, setting up or moving an adb, mdf, ldf file.

I was having a problem locating the difference of location to create a
stored procedure in Access 2000 vs Access 2003, but I believe Sylvain has
answered the questions. Thank you.

 

[M.L. Sco Scofield]

I can confirm what Sylvain said.

The difference is in the version of Access. In 2000, all of the SQL Server
objects, Views, Stored Procedures, and Functions had their own object bar in
the database container. In 2003, they are all lumped together under the
Queries object bar. At least they each have a different icon.

Good luck.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com

 

[lynn_victoria]

LOL Thank you all for your patience! This truly was a case of "lady is the
computer ON?". I never even thougth to look there it was such a departure
from 2000 (which I am used too. ok ok I am blushing!)
thanks all

 

[M.L. Sco Scofield]

No problem. If you don't have both versions side by side, it's hard to
catch.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com