Access 2000 and SQL Server 2000

  • Thread starter Thread starter Joseph
  • Start date Start date
J

Joseph

Hi!

I have couple of questions that I hope someone could
provide me the answers.

1) I know that there are some compatibility issues for the
combination use of Access Project and SQL Server 2000.
Can Access Project be used to create, modify, delete and
save changes tables, views, stored procedures and database
diagrams in SQL Server 2000?

2) Can Access database (in .mdb file format) be used to
create a table as well as append query to the table in SQL
Server 2000?

Any responses will be greatly appreciated! Thank you.

Joseph
 
Joseph said:
Hi!

I have couple of questions that I hope someone could
provide me the answers.

1) I know that there are some compatibility issues for the
combination use of Access Project and SQL Server 2000.
Can Access Project be used to create, modify, delete and
save changes tables, views, stored procedures and database
diagrams in SQL Server 2000?
Click to expand...

Yes, in fact that is kind of the whole idea behind a ADP project. When you
create a query, you are in fact creating a view on the server. Same goes for
creating tables,a nd crating of the relationships...it all works quite the
same of ms-access. This can really shorting the learning curve for some
people. All data, queries and data processing generally occurs on the server
when you create a adp project.

2) Can Access database (in .mdb file format) be used to
create a table as well as append query to the table in SQL
Server 2000?
Click to expand...

Yes, you can execute append queries, or even stored procedures from a mdb
file. However, your queries that you create in the query builder on now
being built in ms-access. If you are going to develop using odbc, and a mdb
file to sql server, you need to have and use the sql server enterprise tools
at hand. (using a ADP eliminates this need).
 
in
2) Can Access database (in .mdb file
format) be used to create a table as
well as append query to the table in
SQL Server 2000?
Click to expand...

I've worked on a good many MDB - Jet - ODBC - server configurations, but
never on any where new server tables were defined from Access. The DBAs for
the server databases did not allow "users" to modify the database designs.
In most of these cases, the DBAs "designed and implemented" the server DBs
using a data modeling tool like ERWIN, so that creating a table from Access
would not have been appropriate.

And, my personal observation would be that it would likely be a great deal
easier to do table and query creation in MS SQL Server's Enterprise Manager
than to try to do so from within Access.

And, whatever method you use, you will have to have the appropriate
permissions and authority on the server database.

Larry Linson
Microsoft Access MVP
 
See comment inline.

Joseph said:
Hi!

I have couple of questions that I hope someone could
provide me the answers.

1) I know that there are some compatibility issues for the
combination use of Access Project and SQL Server 2000.
Can Access Project be used to create, modify, delete and
save changes tables, views, stored procedures and database
diagrams in SQL Server 2000?
Click to expand...

Which version of Access are you using? Access2K has compatibility problem
(minor) with SQL Server2K, 'cause Access 2K was released before SQL
Server2K. MS has published a service pack for Access2K project compatibility
with SQL Server2K. If you are using Access2K2(XP)/2K3, everything should be
fine.
Yes, you can use Access Project to do most basic SQL Server management work,
including create/modify/delete tables/view/stroed procedure/diagram.
Especially when you use SqlServer desktop engine (MSDE), Access project is
very good management tool. As programmer, I've found Accesss Project is good
enough for me to deal with SQL Server/MSDE.
2) Can Access database (in .mdb file format) be used to
create a table as well as append query to the table in SQL
Server 2000?
Click to expand...

I haven't use Access' linked table to SQL Server via ODBC in a real project.
I think, you can use *.mdb to link to SQL Server aand be able to at least
design tables on SQL Server. I don't know whether you can create view/sp on
SQL Server through linked *.mdb file or not. When you use linked *.mdb,
queries you create stays in *.mdb file, I think.

If your back end IS Sql server, I'd definitely choose Access project,
instead of linked *.mdb (unless you inherited complicated Access application
with linked table, designed in Access 97 or older.
 
Back
Top