SQL Table not updating after convert Access 97 - 2000

  • Thread starter Thread starter SharonS
  • Start date Start date
S

SharonS

Are there any known problems regarding SQL tables not
updating and not leaving any error message either?

We have an Access application that was written in Access
97 that contains VB code that worked fine. It's now been
converted to Access 2000. The process runs and doesn't
return any errors, but when the table is checked for the
upates (indicated in reports generated during the process)
the updates aren't there.

Can someone please shed some light as to what we need to
do to fix this?

Thanks,
Sharon
 
I'm unsure about what do you mean exactly with "Updating" and "Converting"
your database; as there are more than one possibility when it come to
Access, MDB files (versions 97 and 2000/2002/2003) and SQL-Server databases.

Maybe a little more explanation of what you have tried exactly will help.

S. L.
 
Hi Sylvain,

Thanks for your response. Hopefully this information will
help you in assisting us.

We have an Access97 application that contains a form which
runs an event coded in MS Visual Basic. The process
basically takes 2 tables. Table A is a linked SQL table,
Table B is an access table created from an outside data
source. A comparison is done between the 2 tables and any
records from Table B that don't exist in Table A are then
added/inserted into Table A. This process works fine in
Access97.

Since we converted this process to Access 2000, the update
doesn't seem to work. The process runs, and doesn't leave
any errors, but the actual linked table (Table A) isn't
updated. The reports that are also generated reflect that
there should be 16 records added to the table.

Please let me know if you need further information.

Thanks in advance for trying to assist us.

Sharon
 
First thing, you should make sure that some VBA errors are not reduced to
silence by some error controling code; like "on error resume next" or "on
error goto label_error_msg": open any module in design view, then in the VBA
windows that will open, select Tools, then Options and finally General,
under which you can set the option "Break on all errors" for "Error
trapping".

This will have the effect of deactivating any "on error resume next" and "on
error goto label" and may well be sufficient to find the culprit.

You should also make that you can update and modify the table A manually by
opening it and trying to edit some values.

In the VBA window, make sure also that "Microsoft DAO 3.6 Object Library" is
set in the references (you will find these settings under Tools) and that it
is specified before "Microsoft ActiveX Data Objects 2.1 Library" (you can
also find 2.5, 2.6, 2.7 or 2.8 instead of the version 2.1). It is very
important that DAO 3.6 be specified before the the OLEDB 2.1 as any
non-fully qualified name for database and recordset objects will take the
nature of the first referenced library.

For exemple, "Dim rs as dao.recordset" is a fully qualified name for a
recordset, but "Dim rs as recordset" is not and may designate either a DAO
or an OLEDB recordset. The mixe-up of references and non-fully qualified
name is probably one of the biggest sources of troubles with Access
2000/2/3.

Finally, make sure that by updating you mean converting an Access 97 MDB
file to the 2000/2/3 versions of MDB files, as Access 2000/2/3 also have
another kind of database: ADP files for dealing exclusively with SQL-Server.
The coding for ADP files have many changes from their MDB counterpart and
this newsgroup is about ADP files. The upsizing wizard can create both
types of files, so I'm not sure about which one you have at this moment.

In the newsgroup news://microsoft.public.access.odbcclientsvr ; you will
probably find more informations related to MDB files and linked tables with
SQL-Server than in this newsgroup.

S. L.
 
Make sure also that you don't have bit datatype with null values; as they
are often troublesome with linked tables.

S. L.
 
Can you please clarify?

How do I check to see if ig's not bit datatype with null
values, is this in reference to the key fields or the
entire table?

Thanks,
S
 
I was refering to using "SQL Server Entreprise Manager". It's often a good
idea to install it on your station when you are dealing with SQL-Server;
especially when you came accross some problem.

With Access, you can also see the datatype by looking at the Design window
for that table. Bit datatype will be designated as Yes/No datatype by Access
and the possibility to have Null value will be translated as having the
property "Required" set to No.

But the design view of Access is somewhat limited in that you cannot save
most of your modifications and that some properties, like Default value, are
often totally wrong.

You can also create an empty new ADP project and etablish a connection to
your SQL-Server and use it to look at the properties of your tables, as the
design mode of ADP for SQL tables is more powerful than the one for MDB
files.

By the way, the "Break on all errors" that I have mentionned in my previous
post have given nothing?

S. L.
 
Back
Top