Patch for SQL Server 2005?

[bubbles]

I am developing an Access frontend for SQL Server 2005.

The version of Access I am using is 2003, and I find that it cannot
update
any table in SQL Server through VBA and SQL.

I was puzzled for quite a while, until I saw a message on one of the
sessions
stating that this version of Access can only update SQL Server 2000
and earlier.

Does anyone know of a patch to this problem? If so, where can I find
it?

Thanks,
Bubbles

[Tom van Stiphout]

On 25 Apr 2007 18:05:56 -0700, bubbles <bu*********@hotmail.com>
wrote:

That's not correct. As long as you have an ODBC driver (SQL Native
Client), you should be able to connect to SQL2005 just fine.
Creating an Access Data Project (ADP) in A2003, connecting to a
SQL2005 database works as well.

-Tom.

>I am developing an Access frontend for SQL Server 2005.

The version of Access I am using is 2003, and I find that it cannot
update
any table in SQL Server through VBA and SQL.

I was puzzled for quite a while, until I saw a message on one of the
sessions
stating that this version of Access can only update SQL Server 2000
and earlier.

Does anyone know of a patch to this problem? If so, where can I find
it?

Thanks,
Bubbles

[bubbles]

On Apr 26, 12:10 pm, Tom van Stiphout <[e-mail]:

>
That's not correct. As long as you have an ODBC driver (SQL Native
Client), you should be able to connect to SQL2005 just fine.
Creating an Access Data Project (ADP) in A2003, connecting to a
SQL2005 database works as well.

-Tom.

I do not want to use an Access project for the following reason:

although I could hide the database window as well as all the database
objects,
and use a startup form, there is no foolproof way for someone to use
the Access
special keys to reveal the objects. Deleting a table in an Access
project really
deletes the SQL Server table.

I Access database, the delete action merely deletes the link to the
table(s).

Connection to the SQL Server is no problem. Its just that Access 2003
does not
allow me to update tables in SQL Server 2005.

Bubbles

[Rick Brandt]

bubbles wrote:

On Apr 26, 12:10 pm, Tom van Stiphout <[e-mail]:

That's not correct. As long as you have an ODBC driver (SQL Native
Client), you should be able to connect to SQL2005 just fine.
Creating an Access Data Project (ADP) in A2003, connecting to a
SQL2005 database works as well.

-Tom.

I do not want to use an Access project for the following reason:

although I could hide the database window as well as all the database
objects,
and use a startup form, there is no foolproof way for someone to use
the Access
special keys to reveal the objects. Deleting a table in an Access
project really
deletes the SQL Server table.

I Access database, the delete action merely deletes the link to the
table(s).

Connection to the SQL Server is no problem. Its just that Access 2003
does not
allow me to update tables in SQL Server 2005.

Bubbles

You're still not clearly stating what you mean. There is nothing about Access
2003 that prevents you from updating DATA in SQL Server 2005. Are you talking
about updating data or the structure of the tables?

I use Access 97, 2000, 2002, and 2003 against SQL Server 2005 tables and see no
difference whatsoever between any of them.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Tom van Stiphout]

On 26 Apr 2007 17:19:15 -0700, bubbles <bu*********@hotmail.com>
wrote:

Perhaps you can't update SQL data tables because you did not specify
primary keys. Access cannot update the correct row if you don't tell
it (using the PK) what makes a row unique.
PKs are a really good idea for several other reasons as well.

The ability to delete a table can be prevented using SQL Server
security.

-Tom.

>On Apr 26, 12:10 pm, Tom van Stiphout <[e-mail]:

>>
That's not correct. As long as you have an ODBC driver (SQL Native
Client), you should be able to connect to SQL2005 just fine.
Creating an Access Data Project (ADP) in A2003, connecting to a
SQL2005 database works as well.

-Tom.

I do not want to use an Access project for the following reason:

although I could hide the database window as well as all the database
objects,
and use a startup form, there is no foolproof way for someone to use
the Access
special keys to reveal the objects. Deleting a table in an Access
project really
deletes the SQL Server table.

I Access database, the delete action merely deletes the link to the
table(s).

Connection to the SQL Server is no problem. Its just that Access 2003
does not
allow me to update tables in SQL Server 2005.

Bubbles

[bubbles]

On Apr 27, 11:23 am, Tom van Stiphout <[e-mail]:

On 26 Apr 2007 17:19:15 -0700, bubbles <[e-mail]>
wrote:

Perhaps you can't update SQL data tables because you did not specify
primary keys. Access cannot update the correct row if you don't tell
it (using the PK) what makes a row unique.
PKs are a really good idea for several other reasons as well.

The ability to delete a table can be prevented using SQL Server
security.

-Tom.

OK, I added an Identity column, and the system prompts me that I
need to use the dbSeeChanges option. Done that too, and it updates
fine.

By the way, I'm still using DAO. Time to move on...

Thanks!
Bubbles

[Bri]

bubbles wrote:

OK, I added an Identity column, and the system prompts me that I
need to use the dbSeeChanges option. Done that too, and it updates
fine.

By the way, I'm still using DAO. Time to move on...

Thanks!
Bubbles

There's no reason to 'move on'. DAO works fine with linked SQL Server
tables. Is it as efficient as ADO (your only other choice when working
with linked tables)? Maybe, maybe not. The differences in performance
are small enough that I wouldn't worry about it. If you know DAO then
you will be more productive than if you start using something you don't
have experience with.

--
Bri

[ManningFan]

On Apr 25, 9:05 pm, bubbles <[e-mail]:

I am developing an Access frontend for SQL Server 2005.

The version of Access I am using is 2003, and I find that it cannot
update
any table in SQL Server through VBA and SQL.

I was puzzled for quite a while, until I saw a message on one of the
sessions
stating that this version of Access can only update SQL Server 2000
and earlier.

Does anyone know of a patch to this problem? If so, where can I find
it?

Thanks,
Bubbles

We use Access 2003 with SQL Server 2005 here and it works fine.
Someone mentioned adding indexes to the SQL Server tables and I can
confirm that if you don't have an index on a SQL Server table then
Access will NOT be able to update the data in the table. No patches
were required. We use both ADO and DAO without any problems.

There IS something funky about dbSeeChanges though. Apparently if
you're using it with OpenRecordset you have to specify the recordset
type as dbOpenDynaset, even though the default is dbOpenDynaset and
you would think it shouldn't have to be specified.

[bubbles]

On May 1, 5:42 am, ManningFan <[e-mail]:

On Apr 25, 9:05 pm, bubbles <[e-mail]:

I am developing an Access frontend for SQL Server 2005.

The version of Access I am using is 2003, and I find that it cannot
update
any table in SQL Server through VBA and SQL.

I was puzzled for quite a while, until I saw a message on one of the
sessions
stating that this version of Access can only update SQL Server 2000
and earlier.

Does anyone know of a patch to this problem? If so, where can I find
it?

Thanks,
Bubbles

We use Access 2003 with SQL Server 2005 here and it works fine.
Someone mentioned adding indexes to the SQL Server tables and I can
confirm that if you don't have an index on a SQL Server table then
Access will NOT be able to update the data in the table. No patches
were required. We use both ADO and DAO without any problems.

There IS something funky about dbSeeChanges though. Apparently if
you're using it with OpenRecordset you have to specify the recordset
type as dbOpenDynaset, even though the default is dbOpenDynaset and
you would think it shouldn't have to be specified.- Hide quoted text -

- Show quoted text -

Yeah, did that (dbOpenDynaset) and it works fine.
Thanx!
Bubbles

[bubbles]

On May 1, 2:58 am, Bri <[e-mail]:

bubbles wrote:

OK, I added an Identity column, and the system prompts me that I
need to use the dbSeeChanges option. Done that too, and it updates
fine.

By the way, I'm still using DAO. Time to move on...

Thanks!
Bubbles

There's no reason to 'move on'. DAO works fine with linked SQL Server
tables. Is it as efficient as ADO (your only other choice when working
with linked tables)? Maybe, maybe not. The differences in performance
are small enough that I wouldn't worry about it. If you know DAO then
you will be more productive than if you start using something you don't
have experience with.

--
Bri

The concern is that some day soon, Microsoft may withdraw support
for DAO...

Bubbles

[Larry Linson]

"bubbles" <bu*********@hotmail.comwrote

The concern is that some day soon, Microsoft
may withdraw support for DAO...

Access 2007 has a new database file format -- it is derived from Jet. The
native language of that new database file format is derived from DAO. And,
to support databases created in previous versions, classic Jet and classic
DAO are included. As far as I know, there was little, if any, change /
improvement to classic ADO (which continues only in the Office-and-related
software world; Visual Studio and the Dot Net Framework use ADO.NET which is
different, built on a different object model).

The hype about ADO being the "wave of the future" for the last few versions
just didn't come to pass. If I were worrying about losing support for an
Access method, I'd be worrying about ADO, not DAO.

Larry Linson
Microsoft Access MVP