Data corruption with SQL server installed

[Stephen Raftery]

Can anyone please help!

I have been running a database for several years, with several VB procedures
in a variety of forms.
I have recently started thinking about upsizing to a server based database,
so I downloaded MSDE200a desktop server emulator to see how it works.
Ever since then, all my VB procedures have gone haywire. i have SQl
statements that refuse to execute, claiming duplicate keys or similar; I get
data conversion type errors because fields that should be simple integers now
appear as dates, and string fields that will not accept string input, and
many such things.

I have tried disabling the MSDE and rebooting, but I cannot get rid of it.
More to the point, I cannot see why the presence of MSDE should affect the
running of pure Access files.

I would appreciate any assistance
I am using MS Office Pro 2003 under WXP Pro

Stephen

 

[Stephen Raftery]

I forgor to mention this is a stand-alone application

Stephen

 

[Sylvain Lafontaine]

Maybe a mixe-up in the references: check the References in the VBA window
and make sure that the Microsoft DAO 3.6 Object Library is selected and that
is displayed *before* the Microsoft ActiveX Data Objects 2.5 library and not
after it.

While in the VBA window, it might also be a good idea to make a full
recompilation: add a dummy reference in the references window and then make
a full recompilation from the Debug menu. Having added a new reference will
force Access to recompile everything.

 

[Stephen Raftery]

Thanks: that may help: the references list includes VBA, MS Access Object
Lib, MS DAO object lib, OLE automation, MS VBA Extensibility, plus an
enormous list of others which are not checked.

I will try the recompile.
Stephen

 

[Stephen Raftery]

I have tried a recompile, but the problem is still there.

By way of explanation, one of teh procedures involves adding a new record to
a table of transaction, for which I use a recordset .AddNew method. When the
..Addnew executes, it fills every field in the table with the value of the
Primary key field (which is an autonumber field), and ignores the
instructions to give the values I specify. Since some of the fields are
strings, and some are boolean, the .Update method fails.
This is quite exasperating.

Stephen

 

[Sylvain Lafontaine]

Did you check the order of the references and make sure that Microsoft DAO
3.6 Object Library is selected and that is displayed *before* the Microsoft
ActiveX Data Objects 2.5 library and not
after it?

If the DAO library comes after the ADO library, you must qualify all
database objects with their full name, ie you must write « Dim rs as
DAO.Recordset » and not just « Dim RS as Recordset »; otherwise an
ADODB.Recordset object will be created because its reference comes firsts.

If not clear from your post if your trouble started after just the
installation of MSDE or after the upsizing of your database to MSDE. From
your first post, I've concluded that you have only installed MSDE without
making any upsizing and that the simple fact of installing MSDE on your
machine have corrupted your MDB database file.

If you are now working with an upsized database and linked tables, you
should say so because many things are different when you are working against
a MSDE or SQL-Server database.

 

[Stephen Raftery]

Yes I checked that, and I have modified the declarations of the database
objects to include the DAO. specification: no joy.

The other thing is as you guessed: I have merely installed MSDE but have not
done anything to the database files themselves. I have since done a system
restore to just before the MSDE installation, and also reinstalled MS Office,
with no cure.

The error seems to be here:

With rDT 'add record to the DestinationTransactions recordset rDT
.AddNew
![TransactionTypeID] = Nz(rSourceXns![TransactionTypeID], 0)
![TransactionDate] = rSourceXns![NextDue]
![AccountID] = rSourceXns![AccountID]
![PayeeID] = rSourceXns![PayeeID]
![IsInvestment] = rSourceXns![IsInvestment]
![Cleared] = False
![Reconciled] = False
![ScheduledXnID] = rSourceXns![XnID]
.Update
end with

I get an error message saying 'duplicate primary key', and checking the
values of the fields shows that they ALL have the same value, viz that of the
primary key of the Source table.

Stephen

 

[Van T. Dinh]

It sounds like something has gone haywire with the MSDE installation that
wrecked you PC, especially that you are not even using the MSDE.

Try the following steps in sequence:

1. Uninstall MSDE
2. If 1 doesn't work, restore your Windows OS to a Restore Point just before
MSDE installation.
3. If 2 doesn't work, try repairing Windows. Check Windows Help on "Repair
Overview". You may need the Windows CD for this step.
4. Re-install Windows from CD or Factory Configuration, e.g. IBM laptops.

Be prepared that you may need to wipe the hard-disk and start everything
from scratch, eventually.

 

[Sylvain Lafontaine]

With the frequent mixup of DAO and ADODB, it's a good habit to always use
the DAO specification not only for database objects but for Recordset
objects, too. It's also possible that the problem comes from your Open
statement and the various options used.

The installation of MSDE comes after the installation of MDAC 2.8, so
uninstalling MSDE or reinstalling Office might change nothing to the fact
that MDAC 2.8 is now installed on your system. However, I don't see any
real problem in having MDAC 2.8 or MSDE installed on your system; all you
have to do should be simply some basic checking to see where the error is
coming from.

For example, you could create an empty database and import everything into
it; as I suspect some form of corruption in your mdb file. You could also
do some tests on a brand new database.

Nearly everyone here is using MDAC 2.8, with or without MSDE or SQL-Server;
so I don't see why you should have a problem with them. However, corruption
problems with MDB are frequent.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Yes I checked that, and I have modified the declarations of the database
objects to include the DAO. specification: no joy.

The other thing is as you guessed: I have merely installed MSDE but have
not
done anything to the database files themselves. I have since done a system
restore to just before the MSDE installation, and also reinstalled MS
Office,
with no cure.

The error seems to be here:

With rDT 'add record to the DestinationTransactions recordset rDT
.AddNew
![TransactionTypeID] = Nz(rSourceXns![TransactionTypeID], 0)
![TransactionDate] = rSourceXns![NextDue]
![AccountID] = rSourceXns![AccountID]
![PayeeID] = rSourceXns![PayeeID]
![IsInvestment] = rSourceXns![IsInvestment]
![Cleared] = False
![Reconciled] = False
![ScheduledXnID] = rSourceXns![XnID]
.Update
end with

I get an error message saying 'duplicate primary key', and checking the
values of the fields shows that they ALL have the same value, viz that of
the
primary key of the Source table.

Stephen

Did you check the order of the references and make sure that Microsoft
DAO
3.6 Object Library is selected and that is displayed *before* the
Microsoft
ActiveX Data Objects 2.5 library and not
after it?

If the DAO library comes after the ADO library, you must qualify all
database objects with their full name, ie you must write « Dim rs as
DAO.Recordset » and not just « Dim RS as Recordset »; otherwise an
ADODB.Recordset object will be created because its reference comes
firsts.

If not clear from your post if your trouble started after just the
installation of MSDE or after the upsizing of your database to MSDE.
From
your first post, I've concluded that you have only installed MSDE without
making any upsizing and that the simple fact of installing MSDE on your
machine have corrupted your MDB database file.

If you are now working with an upsized database and linked tables, you
should say so because many things are different when you are working
against
a MSDE or SQL-Server database.

 

[Stephen Raftery]

I think I might have fixed it!
I noticed that the rogue Primary key that appeareed with the .AddNew method
was not actually the one expected - that is, the next in sequence, but rather
was an old PK for a record that had been deleted years ago.
I reset the Primary Key new value setting to a value higher than all valid
records, and it seems to work OK now!

Thanks for all your help.
How the problem arose I still have no idea.

Stephen

With the frequent mixup of DAO and ADODB, it's a good habit to always use
the DAO specification not only for database objects but for Recordset
objects, too. It's also possible that the problem comes from your Open
statement and the various options used.

The installation of MSDE comes after the installation of MDAC 2.8, so
uninstalling MSDE or reinstalling Office might change nothing to the fact
that MDAC 2.8 is now installed on your system. However, I don't see any
real problem in having MDAC 2.8 or MSDE installed on your system; all you
have to do should be simply some basic checking to see where the error is
coming from.

For example, you could create an empty database and import everything into
it; as I suspect some form of corruption in your mdb file. You could also
do some tests on a brand new database.

Nearly everyone here is using MDAC 2.8, with or without MSDE or SQL-Server;
so I don't see why you should have a problem with them. However, corruption
problems with MDB are frequent.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Yes I checked that, and I have modified the declarations of the database
objects to include the DAO. specification: no joy.

The other thing is as you guessed: I have merely installed MSDE but have
not
done anything to the database files themselves. I have since done a system
restore to just before the MSDE installation, and also reinstalled MS
Office,
with no cure.

The error seems to be here:

With rDT 'add record to the DestinationTransactions recordset rDT
.AddNew
![TransactionTypeID] = Nz(rSourceXns![TransactionTypeID], 0)
![TransactionDate] = rSourceXns![NextDue]
![AccountID] = rSourceXns![AccountID]
![PayeeID] = rSourceXns![PayeeID]
![IsInvestment] = rSourceXns![IsInvestment]
![Cleared] = False
![Reconciled] = False
![ScheduledXnID] = rSourceXns![XnID]
.Update
end with

I get an error message saying 'duplicate primary key', and checking the
values of the fields shows that they ALL have the same value, viz that of
the
primary key of the Source table.

Stephen

Did you check the order of the references and make sure that Microsoft
DAO
3.6 Object Library is selected and that is displayed *before* the
Microsoft
ActiveX Data Objects 2.5 library and not
after it?

If the DAO library comes after the ADO library, you must qualify all
database objects with their full name, ie you must write « Dim rs as
DAO.Recordset » and not just « Dim RS as Recordset »; otherwise an
ADODB.Recordset object will be created because its reference comes
firsts.

If not clear from your post if your trouble started after just the
installation of MSDE or after the upsizing of your database to MSDE.
From
your first post, I've concluded that you have only installed MSDE without
making any upsizing and that the simple fact of installing MSDE on your
machine have corrupted your MDB database file.

If you are now working with an upsized database and linked tables, you
should say so because many things are different when you are working
against
a MSDE or SQL-Server database.


--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


message I have tried a recompile, but the problem is still there.

By way of explanation, one of teh procedures involves adding a new
record
to
a table of transaction, for which I use a recordset .AddNew method.
When
the
.Addnew executes, it fills every field in the table with the value of
the
Primary key field (which is an autonumber field), and ignores the
instructions to give the values I specify. Since some of the fields are
strings, and some are boolean, the .Update method fails.
This is quite exasperating.

Stephen


:

Maybe a mixe-up in the references: check the References in the VBA
window
and make sure that the Microsoft DAO 3.6 Object Library is selected
and
that
is displayed *before* the Microsoft ActiveX Data Objects 2.5 library
and
not
after it.

While in the VBA window, it might also be a good idea to make a full
recompilation: add a dummy reference in the references window and then
make
a full recompilation from the Debug menu. Having added a new reference
will
force Access to recompile everything.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


message Can anyone please help!

I have been running a database for several years, with several VB
procedures
in a variety of forms.
I have recently started thinking about upsizing to a server based
database,
so I downloaded MSDE200a desktop server emulator to see how it
works.
Ever since then, all my VB procedures have gone haywire. i have SQl
statements that refuse to execute, claiming duplicate keys or
similar;
I
get
data conversion type errors because fields that should be simple
integers
now
appear as dates, and string fields that will not accept string
input,
and
many such things.

I have tried disabling the MSDE and rebooting, but I cannot get rid
of
it.
More to the point, I cannot see why the presence of MSDE should
affect
the
running of pure Access files.

I would appreciate any assistance
I am using MS Office Pro 2003 under WXP Pro

Stephen