By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,836 Members | 2,028 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,836 IT Pros & Developers. It's quick & easy.

Access 2003 & SQL Server 2005

P: n/a
We have an Access 2000 MDB with a SQL 7 back end. We are upgrading SQL
Server to SQL 2005, and are considering upgrading to Access 2003. Someone
mentioned that they had heard about some incompatibilities between Access
2003 and SQL Server 2005. Anyone hear of anything? And, if so, would staying
with Access 2000 be better?

Thanks!

Neil
Jan 28 '06 #1
Share this Question
Share on Google+
9 Replies


P: n/a
The comments below are about SQL Express 2005 used on my won personal
computer. My main remote dbs are still SQL 2000 so I don't know if
these problems extend to the big guy.

**** Begin Quote ****
You have connected to a version of SQL Server later than SQL Server
2000. The version of Visual Studio or Access that you are using was
released before the version of SQL Server to which you are connected.
For this reason, you might encounter problems.

Please check with Microsoft to see if there is a service pack that you
should apply to Visual Studio or Office in order to get support for the
version of SQL Server to which you are connected.

You can continue but any new object types might not be enumerated, and
it will not be possible to save any objects or database diagrams that
you create using the Visual Database Tools.
**** End Quote ****

Basically one cannot edit Table Design in SQL 2005 from Access 2003.
Text Sprocs seem to be OK (but not the ugly wizard-grid) although one
gets an error dialog saying changes can't be made ... but they can.

One can do Table Design Changes with VBA/T-SQL or SPROCS. If one
downloads one of MS's freebies (example Microsoft Web Developer Express
Edition) one can do DB Design from there; the upside of this is that
the interface is more advanced than the very basic one in Access 2003.

TTBOMK the actual use of the DB is just fine; naturally new things on
SQL 2005 may not show up the Access Window, just as UDFs don't show (as
I recally) in Access 2K.

Staying with Access 2000 is better than ...
I can't think of anything ... well maybe castration with a rusty spoon
but the jury is still out on that one. Would Access 2000 be better than
Access 2003 for dealing with SQL 2005? Maybe, but it would astound me.

I use Access 2003 with SQL Express 2005, learned how to cope in a
couple of days and never think of the problems as a "problem".

Windows Update told me yesterday I had no Office Updates available but
maybe I had better go look for something that solves this explicitly. I
haven't done this because the problem hasn't bothered me.

Jan 28 '06 #2

P: n/a
Neil wrote:
We have an Access 2000 MDB with a SQL 7 back end. We are upgrading SQL
Server to SQL 2005, and are considering upgrading to Access 2003.
Someone mentioned that they had heard about some incompatibilities
between Access 2003 and SQL Server 2005. Anyone hear of anything?
And, if so, would staying with Access 2000 be better?


Those limitations are restricted to ADPs. I don't think you will see any
difference with an MDB/ODBC setup.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Jan 28 '06 #3

P: n/a
I keep forgetting that most people use this setup! ...
Hmmmmmm ...
TTBOMK installing MS-SQL 2005 Express requires the removal of all
MS-SQL 2000 thingmes. Does this include EM? I don't know. If it does
then how does the ODBC person manage the DB? The Microsoft Web
Developer Express Edition and similar utility will not do MS-SQL 2000.
So perhaps, the ODBC person who downloads that will have to use a
parallel (to his/her MDB) ADP to manage the MS-SQL 2000 db.
Well I'm not going to ODBC so I'll keep out of that. Yeah, I know
"Knowlegeable MS Insiders" recommend it as the way to go. Well,
"Knowlegeable MS Insiders" recommended ADPs too until the users showed
they were fundamentally flawed. How can a company recommend something
that is so retrograde, so twentieth century, so clumsy? I suppose it's
because they got caught with their ADP pants down. It reminds me a lot
of General Motors and their push rod engine. How's General Motors doing
thses day anyway?

Jan 28 '06 #4

P: n/a
Lyle Fairfield wrote:
I keep forgetting that most people use this setup! ...
Hmmmmmm ...
TTBOMK installing MS-SQL 2005 Express requires the removal of all
MS-SQL 2000 thingmes. Does this include EM? I don't know. If it does
then how does the ODBC person manage the DB?
The OP indicated an upgrade from full SQL Server 2000 to 2005 so I assume that
those charged with managing the db will have the full SS client toolset
installed.
The Microsoft Web
Developer Express Edition and similar utility will not do MS-SQL 2000.
So perhaps, the ODBC person who downloads that will have to use a
parallel (to his/her MDB) ADP to manage the MS-SQL 2000 db.
Well I'm not going to ODBC so I'll keep out of that. Yeah, I know
"Knowlegeable MS Insiders" recommend it as the way to go. Well,
"Knowlegeable MS Insiders" recommended ADPs too until the users showed
they were fundamentally flawed. How can a company recommend something
that is so retrograde, so twentieth century, so clumsy? I suppose it's
because they got caught with their ADP pants down. It reminds me a lot
of General Motors and their push rod engine. How's General Motors
doing thses day anyway?


Yeah, I can't believe we're still using mice, keyboards, and 2-D displays either
:-)

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com


Jan 28 '06 #5

P: n/a
On Sat, 28 Jan 2006 12:18:05 GMT, "Rick Brandt" <ri*********@hotmail.com> wrote:
Yeah, I can't believe we're still using mice, keyboards, and 2-D displays either
:-)


Not for long -
http://www.snopes.com/photos/advertisements/pcpen.asp
Wayne Gillespie
Gosford NSW Australia
Jan 28 '06 #6

P: n/a
"Lyle Fairfield" <ly***********@aim.com> wrote in
news:11**********************@g47g2000cwa.googlegr oups.com:
How can a company recommend something
that is so retrograde, so twentieth century, so clumsy? I suppose
it's because they got caught with their ADP pants down. It reminds
me a lot of General Motors and their push rod engine. How's
General Motors doing thses day anyway?


Well, it's losing money hand over fist, having just posted the
second biggest loss in its history.

Next question?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jan 28 '06 #7

P: n/a
Even though I'm using an MDB front end, I do use an ADP file for editing the
db -- tables, views, sps. I have EM, and I use that for some things. But I
find using an ADP file much easier in terms of the interface. So I guess
that would be a problem with SQL 2005.

On the upside, the upgrade to SQL 2005 would probably be months away. By
that time, hopefully MS will have provided a patch.

Thanks!

Neil
"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
The comments below are about SQL Express 2005 used on my won personal
computer. My main remote dbs are still SQL 2000 so I don't know if
these problems extend to the big guy.

**** Begin Quote ****
You have connected to a version of SQL Server later than SQL Server
2000. The version of Visual Studio or Access that you are using was
released before the version of SQL Server to which you are connected.
For this reason, you might encounter problems.

Please check with Microsoft to see if there is a service pack that you
should apply to Visual Studio or Office in order to get support for the
version of SQL Server to which you are connected.

You can continue but any new object types might not be enumerated, and
it will not be possible to save any objects or database diagrams that
you create using the Visual Database Tools.
**** End Quote ****

Basically one cannot edit Table Design in SQL 2005 from Access 2003.
Text Sprocs seem to be OK (but not the ugly wizard-grid) although one
gets an error dialog saying changes can't be made ... but they can.

One can do Table Design Changes with VBA/T-SQL or SPROCS. If one
downloads one of MS's freebies (example Microsoft Web Developer Express
Edition) one can do DB Design from there; the upside of this is that
the interface is more advanced than the very basic one in Access 2003.

TTBOMK the actual use of the DB is just fine; naturally new things on
SQL 2005 may not show up the Access Window, just as UDFs don't show (as
I recally) in Access 2K.

Staying with Access 2000 is better than ...
I can't think of anything ... well maybe castration with a rusty spoon
but the jury is still out on that one. Would Access 2000 be better than
Access 2003 for dealing with SQL 2005? Maybe, but it would astound me.

I use Access 2003 with SQL Express 2005, learned how to cope in a
couple of days and never think of the problems as a "problem".

Windows Update told me yesterday I had no Office Updates available but
maybe I had better go look for something that solves this explicitly. I
haven't done this because the problem hasn't bothered me.

Jan 30 '06 #8

P: n/a
> Staying with Access 2000 is better than ...
I can't think of anything ... well maybe castration with a rusty spoon
but the jury is still out on that one.
Re. this, I'm been working with my A2000 MDB with a SQL 7 back end under the
assumption that A2000 would work about the same as A2002/3, with perhaps
minimal advantages with A2002/3. Sounds like you're saying otherwise. I'd be
very interested in hearing specifically in what ways A2003 would be better.

My reason for asking is that several months ago when I recommended upgrading
to A2003, the project manager was open to it; but then the sys admin guy
(who buys all the software) shot it down, saying that Office 2000 is still
the standard in most offices; there was no need for the expense; etc. (This
fellow knows basically nothing about databases, and is under the assumption
that it's OK for users to have mixed versions of Office (some with this
version, some with that), and they should all work exactly the same.)

So, since the project manager is open to upgrading, if I can just give him
some specifics as to why A2003 would be better than A2000 for our SQL back
end, that would override the sys admin's stupidity.

Thanks,

Neil

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com... The comments below are about SQL Express 2005 used on my won personal
computer. My main remote dbs are still SQL 2000 so I don't know if
these problems extend to the big guy.

**** Begin Quote ****
You have connected to a version of SQL Server later than SQL Server
2000. The version of Visual Studio or Access that you are using was
released before the version of SQL Server to which you are connected.
For this reason, you might encounter problems.

Please check with Microsoft to see if there is a service pack that you
should apply to Visual Studio or Office in order to get support for the
version of SQL Server to which you are connected.

You can continue but any new object types might not be enumerated, and
it will not be possible to save any objects or database diagrams that
you create using the Visual Database Tools.
**** End Quote ****

Basically one cannot edit Table Design in SQL 2005 from Access 2003.
Text Sprocs seem to be OK (but not the ugly wizard-grid) although one
gets an error dialog saying changes can't be made ... but they can.

One can do Table Design Changes with VBA/T-SQL or SPROCS. If one
downloads one of MS's freebies (example Microsoft Web Developer Express
Edition) one can do DB Design from there; the upside of this is that
the interface is more advanced than the very basic one in Access 2003.

TTBOMK the actual use of the DB is just fine; naturally new things on
SQL 2005 may not show up the Access Window, just as UDFs don't show (as
I recally) in Access 2K.

Staying with Access 2000 is better than ...
I can't think of anything ... well maybe castration with a rusty spoon
but the jury is still out on that one. Would Access 2000 be better than
Access 2003 for dealing with SQL 2005? Maybe, but it would astound me.

I use Access 2003 with SQL Express 2005, learned how to cope in a
couple of days and never think of the problems as a "problem".

Windows Update told me yesterday I had no Office Updates available but
maybe I had better go look for something that solves this explicitly. I
haven't done this because the problem hasn't bothered me.

Jan 30 '06 #9

P: n/a
As noted to Lyle in this thread, though I'm using an MDB file, I use an ADP
for editing tables, views, and stored procedures, as I find it more
navigatable than EM. Sounds like I'd be screwed in that regard, right?
"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:uG*****************@newssvr27.news.prodigy.ne t...
Neil wrote:
We have an Access 2000 MDB with a SQL 7 back end. We are upgrading SQL
Server to SQL 2005, and are considering upgrading to Access 2003.
Someone mentioned that they had heard about some incompatibilities
between Access 2003 and SQL Server 2005. Anyone hear of anything?
And, if so, would staying with Access 2000 be better?


Those limitations are restricted to ADPs. I don't think you will see any
difference with an MDB/ODBC setup.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Jan 30 '06 #10

This discussion thread is closed

Replies have been disabled for this discussion.