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

Upgrade path to MSSQL2000

P: n/a
I have a continuous form that works just swell in A97.

The datasource is a linked table in the backend database that has an
ID field as the PK and no other indices.

I take that table from the backend Access database and copy the data
in it to an instance of MSSQL 2000 I have established on the same
machine (with only one index, the pk on the ID field), create a ODBC
connection to that MSSQL 2000 database on my computer and then delete
the reference in the front end to the Access backend and replace it
with a link to the MSSQL 2000 table in question.

All seems to go swimmingly. Looks can be deceiving, I've found out.

My continuous form will bring up the information it is supposed to
exactly ONCE. After that, all records display the dreaded #deleted in
each and every column.

Using another method of accessing the table in the MSSQL 2000
database, I can see that the original records are there and they
respond nicely to a query like:

SELECT * FROM MYTABLE

Back in Access, when I go directly into the table from the Tables tab
of the main database window I also see nothing but a sea of
"#deleted"'s.

When I try to put my cursor in one of the cells of a deleted record
and restore its contents to something other than "#deleted" I am
graced with an Access error (3329) indicating that the record has been
deleted by another user.

All well and good, except for the facts that:

1) My other utility (WINSQL) tells me that the records are alive and
well when I run the query "SELECT * FROM MYTABLE" and then view the
results.

2) My method of linking the ODBC table is the same, whether in Access
or WINSQL, in that I am specifying the user/password/database/table
combination in my connect string:

strConnect = "ODBC;DRIVER=SQL
Server;Server=MYSERVER\MYINSTANCE;UID=me;PWD=mystr ongPASSWORD123$;DATABASE=tempdb;Network=DBMSSOCN;"
Set CurrTableTDF = CurrentDb.CreateTableDef("MYTABLE",
dbAttachSavePWD)
CurrTableTDF.Connect = strConnect
CurrTableTDF.SourceTableName = "MYTABLE"
CurrentDb.TableDefs.Append CurrTableTDF

At this point the MYTABLE link flourishes amongst my Access tables..
Yes, I know if I rerun this code I need to delete the link first.

I have indeed attempted to find something on Google that references
this, but I have failed.

If anybody has any ideas I would greatly appreciate it.

Thanks

DJ
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Morning DJ wrote:
I have a continuous form that works just swell in A97.

The datasource is a linked table in the backend database that has an
ID field as the PK and no other indices.

I take that table from the backend Access database and copy the data
in it to an instance of MSSQL 2000 I have established on the same
machine (with only one index, the pk on the ID field), create a ODBC
connection to that MSSQL 2000 database on my computer and then delete
the reference in the front end to the Access backend and replace it
with a link to the MSSQL 2000 table in question.

All seems to go swimmingly. Looks can be deceiving, I've found out.

My continuous form will bring up the information it is supposed to
exactly ONCE. After that, all records display the dreaded #deleted in
each and every column.

Using another method of accessing the table in the MSSQL 2000
database, I can see that the original records are there and they
respond nicely to a query like:

SELECT * FROM MYTABLE

Back in Access, when I go directly into the table from the Tables tab
of the main database window I also see nothing but a sea of
"#deleted"'s.

When I try to put my cursor in one of the cells of a deleted record
and restore its contents to something other than "#deleted" I am
graced with an Access error (3329) indicating that the record has been
deleted by another user.

All well and good, except for the facts that:

1) My other utility (WINSQL) tells me that the records are alive and
well when I run the query "SELECT * FROM MYTABLE" and then view the
results.

2) My method of linking the ODBC table is the same, whether in Access
or WINSQL, in that I am specifying the user/password/database/table
combination in my connect string:

strConnect = "ODBC;DRIVER=SQL
Server;Server=MYSERVER\MYINSTANCE;UID=me;PWD=mystr ongPASSWORD123$;DATABASE=tempdb;Network=DBMSSOCN;"
Set CurrTableTDF = CurrentDb.CreateTableDef("MYTABLE",
dbAttachSavePWD)
CurrTableTDF.Connect = strConnect
CurrTableTDF.SourceTableName = "MYTABLE"
CurrentDb.TableDefs.Append CurrTableTDF

At this point the MYTABLE link flourishes amongst my Access tables..
Yes, I know if I rerun this code I need to delete the link first.

I have indeed attempted to find something on Google that references
this, but I have failed.

If anybody has any ideas I would greatly appreciate it.


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try running a TableDefs.Refresh after appending the new table
definition.

I've read, somewhere, I can't find it now, that DAO 3.x has some
problems w/ SQL 2000. Not sure if this is your problem.

I've had the #Deleted# problem before w/ SQL 6.5 & Access 97 (DAO 3.x).
The solution was to get the most up to date SQL Server 6.5 drivers (can
get SQL'r 2000 drivers on MS "Download" website in MDAC package).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQgQVyYechKqOuFEgEQLYeACfchDPD/E2kB8+O7dFOdrvEB7MohUAnR+n
t0PqZU5Fb8Av3sLVrPnnD4WR
=kGg8
-----END PGP SIGNATURE-----
Nov 13 '05 #2

P: n/a
In response: MGFoster wrote:

On Fri, 04 Feb 2005 22:56:32 GMT, ac*********@radio.com.invalid
(Morning DJ) wrote:
I have a continuous form that works just swell in A97.

The datasource is a linked table in the backend database that has an
ID field as the PK and no other indices.

I take that table from the backend Access database and copy the data
in it to an instance of MSSQL 2000 I have established on the same
machine (with only one index, the pk on the ID field), create a ODBC
connection to that MSSQL 2000 database on my computer and then delete
the reference in the front end to the Access backend and replace it
with a link to the MSSQL 2000 table in question.

My continuous form will bring up the information it is supposed to
exactly ONCE. After that, all records display the dreaded #deleted in
each and every column.

Using another method of accessing the table in the MSSQL 2000
database, I can see that the original records are there and they
respond nicely to a query like:

SELECT * FROM MYTABLE

If anybody has any ideas I would greatly appreciate it.

Thanks

DJ
Try running a TableDefs.Refresh after appending the new table
definition.
No joy.
I've read, somewhere, I can't find it now, that DAO 3.x has some
problems w/ SQL 2000. Not sure if this is your problem.
Hopefully not, because I have no cure for that one.
I've had the #Deleted# problem before w/ SQL 6.5 & Access 97 (DAO 3.x).
The solution was to get the most up to date SQL Server 6.5 drivers
(can get SQL'r 2000 drivers on MS "Download" website in MDAC package). MGFoster:::mgf00 <at> earthlink <decimal-point> net


I had installed MSDE just the other day, so MDAC pretty current.
Nonetheless, went and installed MDAC 2.8 [after living through the
hell of "Windows verification" where it wouldn't take my 25 character
code that I was reading off my computer so I had to do other things].
Well, that obliterated my (thankfully test) tables within the database
I was using.

So I rebuilt them.

No joy.

Any other ideas other than upgrading to A2002?

My goal is to migrate to a SQL backend which I can locate on a regular
windows computer so that 3 users in different locations can use the
same database, or, in the alternative, they would each use their own
version of the database and then I would use merge replication to
synch the databases. In case it matters, I have long ago given up on
Access replication as being too persnickity.

I'm thinking that maybe I shouldn't be using the sa user id on the
database since there is a separate user name in effect under the .mdw
file. Might that be the problem?

Thanks

DJ
Nov 13 '05 #3

P: n/a
Morning DJ,

Check that you don't have any BIGINT or DECIMAL datatypes as there are a few
SQL 2000 data types that Access 97 cannot handle well.

Regards
SB

"Morning DJ" <ac*********@radio.com.invalid> wrote in message
news:42***************@news.INDIVIDUAL.NET...
In response: MGFoster wrote:

On Fri, 04 Feb 2005 22:56:32 GMT, ac*********@radio.com.invalid
(Morning DJ) wrote:
I have a continuous form that works just swell in A97.

The datasource is a linked table in the backend database that has an
ID field as the PK and no other indices.

I take that table from the backend Access database and copy the data
in it to an instance of MSSQL 2000 I have established on the same
machine (with only one index, the pk on the ID field), create a ODBC
connection to that MSSQL 2000 database on my computer and then delete
the reference in the front end to the Access backend and replace it
with a link to the MSSQL 2000 table in question.

My continuous form will bring up the information it is supposed to
exactly ONCE. After that, all records display the dreaded #deleted in
each and every column.

Using another method of accessing the table in the MSSQL 2000
database, I can see that the original records are there and they
respond nicely to a query like:

SELECT * FROM MYTABLE

If anybody has any ideas I would greatly appreciate it.

Thanks

DJ

Try running a TableDefs.Refresh after appending the new table
definition.


No joy.
I've read, somewhere, I can't find it now, that DAO 3.x has some
problems w/ SQL 2000. Not sure if this is your problem.


Hopefully not, because I have no cure for that one.
I've had the #Deleted# problem before w/ SQL 6.5 & Access 97 (DAO 3.x).
The solution was to get the most up to date SQL Server 6.5 drivers
(can get SQL'r 2000 drivers on MS "Download" website in MDAC package).

MGFoster:::mgf00 <at> earthlink <decimal-point> net


I had installed MSDE just the other day, so MDAC pretty current.
Nonetheless, went and installed MDAC 2.8 [after living through the
hell of "Windows verification" where it wouldn't take my 25 character
code that I was reading off my computer so I had to do other things].
Well, that obliterated my (thankfully test) tables within the database
I was using.

So I rebuilt them.

No joy.

Any other ideas other than upgrading to A2002?

My goal is to migrate to a SQL backend which I can locate on a regular
windows computer so that 3 users in different locations can use the
same database, or, in the alternative, they would each use their own
version of the database and then I would use merge replication to
synch the databases. In case it matters, I have long ago given up on
Access replication as being too persnickity.

I'm thinking that maybe I shouldn't be using the sa user id on the
database since there is a separate user name in effect under the .mdw
file. Might that be the problem?

Thanks

DJ

Nov 13 '05 #4

P: n/a
On Sat, 05 Feb 2005 07:46:41 GMT, "Scott Berry"
<sw*****@bigpond.net.au> wrote:
Morning DJ,

Check that you don't have any BIGINT or DECIMAL datatypes as there are a few
SQL 2000 data types that Access 97 cannot handle well.
BINGO! The BIGINT did me in. For some reason I thought that I could
use BIGINT for Long Integer. Wrong. Long Integer is just INT.

THANKS!

DJ
"Morning DJ" <ac*********@radio.com.invalid> wrote in message
news:42***************@news.INDIVIDUAL.NET...
In response: MGFoster wrote:

On Fri, 04 Feb 2005 22:56:32 GMT, ac*********@radio.com.invalid
(Morning DJ) wrote:
I have a continuous form that works just swell in A97.

The datasource is a linked table in the backend database that has an
ID field as the PK and no other indices.

I take that table from the backend Access database and copy the data
in it to an instance of MSSQL 2000 I have established on the same
machine (with only one index, the pk on the ID field), create a ODBC
connection to that MSSQL 2000 database on my computer and then delete
the reference in the front end to the Access backend and replace it
with a link to the MSSQL 2000 table in question.

My continuous form will bring up the information it is supposed to
exactly ONCE. After that, all records display the dreaded #deleted in
each and every column.

Using another method of accessing the table in the MSSQL 2000
database, I can see that the original records are there and they
respond nicely to a query like:

SELECT * FROM MYTABLE

If anybody has any ideas I would greatly appreciate it.

Thanks

DJ

Try running a TableDefs.Refresh after appending the new table
definition.


No joy.
I've read, somewhere, I can't find it now, that DAO 3.x has some
problems w/ SQL 2000. Not sure if this is your problem.


Hopefully not, because I have no cure for that one.
I've had the #Deleted# problem before w/ SQL 6.5 & Access 97 (DAO 3.x).
The solution was to get the most up to date SQL Server 6.5 drivers
(can get SQL'r 2000 drivers on MS "Download" website in MDAC package).

MGFoster:::mgf00 <at> earthlink <decimal-point> net


I had installed MSDE just the other day, so MDAC pretty current.
Nonetheless, went and installed MDAC 2.8 [after living through the
hell of "Windows verification" where it wouldn't take my 25 character
code that I was reading off my computer so I had to do other things].
Well, that obliterated my (thankfully test) tables within the database
I was using.

So I rebuilt them.

No joy.

Any other ideas other than upgrading to A2002?

My goal is to migrate to a SQL backend which I can locate on a regular
windows computer so that 3 users in different locations can use the
same database, or, in the alternative, they would each use their own
version of the database and then I would use merge replication to
synch the databases. In case it matters, I have long ago given up on
Access replication as being too persnickity.

I'm thinking that maybe I shouldn't be using the sa user id on the
database since there is a separate user name in effect under the .mdw
file. Might that be the problem?

Thanks

DJ



Nov 13 '05 #5

P: n/a
In addition, to that solution, you want to ALWAYS expose the timestamp field
in the query to the ms-access form, (especially the continues form).

If you don't do this, then often child (sub forms) will not work. Ms-access
(and a lot of odbc clients) use the timestamp field to figure out of things
have been changed. So, as a rule of thumb...include and EXPOSE the timestamp
field to ms-access, and things will work a LOT smoother...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal
Nov 13 '05 #6

P: n/a
On Sun, 06 Feb 2005 05:04:14 GMT, "Albert D. Kallal" <ka****@msn.com>
wrote:
In addition, to that solution, you want to ALWAYS expose the timestamp field
in the query to the ms-access form, (especially the continues form).

If you don't do this, then often child (sub forms) will not work. Ms-access
(and a lot of odbc clients) use the timestamp field to figure out of things
have been changed. So, as a rule of thumb...include and EXPOSE the timestamp
field to ms-access, and things will work a LOT smoother...


Thanks for the tip. I will be sure to add a timestamp field to every
SQL table and to ensure that every request to the server includes that
field.

DJ

Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.