473,327 Members | 2,074 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Upgrade path to MSSQL2000

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
6 1568
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Rene Kadner | last post by:
Hi, Zu Test-/Entwicklungszwecken mache ich eine Rücksicherung von der produktiven MSSQL7 Datenbank auf einen MSSQL2000. Das klappte bisher immer Problemlos. Neuerdings erhalte ich aber aus...
1
by: Inquisitive | last post by:
Hello! Does anybody know whether mssql2000 and emc mirrorvew _certified_ for joint work? (Mirrorview is a fc-based remote mirroring solution) I mean is it supported from the MS point of...
3
by: heynothanks | last post by:
(MSSQL2000) I have read the transaction/locking sections in the MS-help, online and several books. What I want to understand is the transaction behavior in single statements . If I have a...
36
by: Tim | last post by:
Is there a way to upgrade from Visual C++ Net 2002 to Visual C++ Net 2003? The 2002 version does not provide a Windows Forms Designer. I can't find any upgrade package on Microsoft's website. ...
6
by: Joseph Geretz | last post by:
I recently upgraded my server to Windows 2003. The first thing I noticed is that my sample WebService pages no longer worked. The Invoke test button is missing. This is addresed by the following KB...
0
by: Marek Wierzbicki | last post by:
Drodzy czytelnicy listy Szukam specjalisty znaj±cego się perfekcyjne na MSSQL2000. Interesuje mnie wiedza typu "inside", czyli struktury plików itp. Konkretnie chodzi mi o organizacje wewnętrzn±...
0
by: chowdhp | last post by:
Hi, I am trying to migrate our server MSSQL2000 to MSSQL2005 and getting the following errors. 1. When I tried to restore the Master DB in the test server(single user mode) from a backup file...
1
by: -Lost | last post by:
I have been using a prepackaged WAMP (Windows Apache MySQL PHP) installation and along the way (over several years) have modified this and that, added vhosts, modified php.ini the way I want, set...
0
by: comp21 | last post by:
Hi, Now, I have retrieved or imported data from excel spreadsheet to vb6 application. Now I want to compare this part of data with the one already existing in mssql2000 server(table name being...
3
by: Erwin Moller | last post by:
Hi Group, I developed a intranet site using MSSQL7/win2000 some time ago. The target environment used MSSQL2000/8. We were (almost painlessly) able to import the db-scheme and data from 7 to 8....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shćllîpôpď 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.