Re: Data corruption with SQL server installed

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


"Sylvain Lafontaine" wrote:

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


"Stephen Raftery" <StephenRaftery@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:65D626FF-CD1F-42C3-99E3-3D4684F438E0@xxxxxxxxxxxxxxxx
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

"Sylvain Lafontaine" wrote:

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


"Stephen Raftery" <StephenRaftery@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:F2C967FF-2849-4791-9A5A-425A5FCEBEBF@xxxxxxxxxxxxxxxx
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" wrote:

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


"Stephen Raftery" <StephenRaftery@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:499F3010-1381-4875-B1A4-E62FCB2206F8@xxxxxxxxxxxxxxxx
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










.

Relevant Pages

  • Re: Data corruption with SQL server installed
    ... the DAO specification not only for database objects but for Recordset ... The installation of MSDE comes after the installation of MDAC 2.8, ... you could create an empty database and import everything into ...
    (microsoft.public.access.tablesdbdesign)
  • Re: STS 2.0 language problems
    ... MSDE 2000 and WMSDE?? ... WMSDE is the default database system that comes with WSS and which is ... installed if you do a standard default installation of WSS. ...
    (microsoft.public.sharepoint.windowsservices)
  • Re: stored procedure performance
    ... SELECT CAST(COUNT(DISTINCT GroupId) AS FLOAT) / COUNTAS GroupPct ... So we decided to use MSDE. ... the limitation of MSDE is Max. 16 Database and max. 2 GB per ... > Your table has no Primary Key, ...
    (microsoft.public.sqlserver.programming)
  • Re: Enterprise manager
    ... I have made one test database suspect and when i ... >> Eneterprise manager with MSDE? ... installation of SQL server? ...
    (microsoft.public.sqlserver.msde)
  • Re: I need a simple solution (read on inside)
    ... table and an create an empty database. ... If I drop firebird and goto access will I need to purchase MS ... >Also, MSDE is another database engine choice, but MSDE requires end user ... >installation. ...
    (microsoft.public.dotnet.languages.vb)