Big table looses Data - Access loss of Confidence

[HelmutMunich]

Hello, I am Helmut,

my database for multiuser is Frontend/Backend. The turnover file
is browsed by VBA for Article numbers to be found in the article table.
I use DAO 3.6 lib. The sql Statement uses the article number of the
turnover file - with 29 Fields. After record 187 or so, the Article
number, read from the turnover file

shows only a string of ten blanks.

Looking into the Turnover database shows clearly Article numbers.

They com from a AS400. OK. Some of the article numbers come with
appended blanks like "T005005005 ". Which makes me cautious.

Not so the missing article number of the turnaround table.

And all the following Art numbers (in the turnover table) are about ten
blanks. The Backend database which contains turnover and article is
repaired and compressed. Not so the frontend. I don't like to loose the
passwords and keywords. I use Access2000, in a multiuser citrix
environment, english version.

'---------cut here---
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("D:\CustomersDB\DATA14.MDB")
Set UST = db.OpenRecordset("Umsätze", dbOpenTable) 'turnover
Set ART = db.OpenRecordset("Article", dbOpenTable)
UST.MoveLast
ustAnzrecs = UST.RecordCount
UST.MoveFirst
For ustRecCount = 1 To ustAnzrecs
If Left$(UST.Fields(16), 1) <> " " Then
If UST.Updatable Then
UMSART = UST!ART ' get Articlenumber from turnaround
Descr = UST!descrizione ' and description
' Data type mismatch Solution:eg.: "[WorkOrderID] = " & Chr(34) &
Worder & Chr(34) & ";"
SQLSelect = "SELECT ARTICLE.ART FROM ARTICLE in
'D:\Customersdb\Data14.mdb' " & _
"WHERE ARTicle.ART = " & Chr(34) & UMSART & Chr(34) & " "
Set ART = db.OpenRecordset(SQLSelect,dbOpenForwardOnly)
If Not ART.EOF Then 'found
ARTART = ART!ART 'get Articlenumber Just a Dummy
Else
If ART.Updatable Then '*** not found/new Record
End If
ART.AddNew
ART!ART = UMSART
ART!descrizione = Descr
ART.Update
'Wend
End If
UST.MoveNext
End If
End If
Next ' For read Loop Umsätze / Turnover

ART.Close
Set ART = Nothing
UST.Close
Set UST = Nothing
--- cut here---

2nd difficulty:
And my users report missing data in subforms of contact persons
in my address file subtable contact persons.
Inter National Help ... is appreciated.

Helmut

[Steve Jorgensen]

I presume you regularly run a repair and compact operation on your database.

On 20 May 2005 18:01:31 -0700, "HelmutMunich" <he**************@12move.de>
wrote:

Hello, I am Helmut,

my database for multiuser is Frontend/Backend. The turnover file
is browsed by VBA for Article numbers to be found in the article table.
I use DAO 3.6 lib. The sql Statement uses the article number of the
turnover file - with 29 Fields. After record 187 or so, the Article
number, read from the turnover file

shows only a string of ten blanks.

Looking into the Turnover database shows clearly Article numbers.

They com from a AS400. OK. Some of the article numbers come with
appended blanks like "T005005005 ". Which makes me cautious.

Not so the missing article number of the turnaround table.

And all the following Art numbers (in the turnover table) are about ten
blanks. The Backend database which contains turnover and article is
repaired and compressed. Not so the frontend. I don't like to loose the
passwords and keywords. I use Access2000, in a multiuser citrix
environment, english version.

'---------cut here---
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("D:\CustomersDB\DATA14.MDB")
Set UST = db.OpenRecordset("Umsätze", dbOpenTable) 'turnover
Set ART = db.OpenRecordset("Article", dbOpenTable)
UST.MoveLast
ustAnzrecs = UST.RecordCount
UST.MoveFirst
For ustRecCount = 1 To ustAnzrecs
If Left$(UST.Fields(16), 1) <> " " Then
If UST.Updatable Then
UMSART = UST!ART ' get Articlenumber from turnaround
Descr = UST!descrizione ' and description
' Data type mismatch Solution:eg.: "[WorkOrderID] = " & Chr(34) &
Worder & Chr(34) & ";"
SQLSelect = "SELECT ARTICLE.ART FROM ARTICLE in
'D:\Customersdb\Data14.mdb' " & _
"WHERE ARTicle.ART = " & Chr(34) & UMSART & Chr(34) & " "
Set ART = db.OpenRecordset(SQLSelect,dbOpenForwardOnly)
If Not ART.EOF Then 'found
ARTART = ART!ART 'get Articlenumber Just a Dummy
Else
If ART.Updatable Then '*** not found/new Record
End If
ART.AddNew
ART!ART = UMSART
ART!descrizione = Descr
ART.Update
'Wend
End If
UST.MoveNext
End If
End If
Next ' For read Loop Umsätze / Turnover

ART.Close
Set ART = Nothing
UST.Close
Set UST = Nothing
--- cut here---

2nd difficulty:
And my users report missing data in subforms of contact persons
in my address file subtable contact persons.
Inter National Help ... is appreciated.

Helmut

[lylefair]

HelmutMunich wrote:

Hello, I am Helmut,

my database for multiuser is Frontend/Backend. ...

Hello, I am Lyle:

The code you have posted is inadequate, convoluted, redundant and
generally poor. It is inappropriate for you to attribute "Access loss
of confidence" to its failure to do what you want.

IMO, if you were to post the pertinent parts of the design of your two
tables, and a clear description of what you want to do, it's likely
that some JET SQL guru here in CDMA will suggest a short SQL Script
(Query SQL) which will effect this.

[David W. Fenton]

ly******@yahoo.ca wrote in
news:11*********************@g47g2000cwa.googlegro ups.com:

HelmutMunich wrote:

Hello, I am Helmut,

my database for multiuser is Frontend/Backend. ...
The code you have posted is inadequate, convoluted, redundant and
generally poor. It is inappropriate for you to attribute "Access
loss of confidence" to its failure to do what you want.

I seldom agree with Lyle, but on this one, I'm in 100% agreement.
IMO, if you were to post the pertinent parts of the design of your
two tables, and a clear description of what you want to do, it's
likely that some JET SQL guru here in CDMA will suggest a short
SQL Script (Query SQL) which will effect this.

The example code shows sequential thinking instead of using SQL to
retrieve the needed data. It's also confused, since you open a
tabletype recordset with recordset variable ART, then turn around
and, without closing the first recordset, assign a different
recordset to the same variable.

I certainly also don't see any reason why you need to use TableType
recordsets, since you're not using SEEK to navigate (which is the
only worthwhile reason to use them).

Also, I have no idea what your code is doing. It looks like
something that a simple UPDATE query ought to be able to do, or that
ought to be done in a form/subform in the first place.

Describe what you're trying to do and how the data in the two tables
relates. Then, perhaps there'd be a chance that we can help untangle
this spaghetti.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[HelmutMunich]

Hello,

now I compacted and repaired, backend and frontend, changed the users
rights, avoided to run the password manager - which caused a lot of
trouble, until I fond out - in this user group that I will never run
this dangerous part.

Then I tried to change my statements, started the test and: since
turnover record number 208 I could not see any Art numbers any more.

Actual code:

----cut ---

Set ws = DBEngine.Workspaces(0) 'comes from
Set db = ws.OpenDatabase("D:\CustomersDB\DATA14.MDB") 'instead
currentDB()
Set UST = db.OpenRecordset("Umsätze", dbOpenTable)
'turnaround(dbOpenTable for seek only)
UST.MoveLast
ustAnzrecs = UST.RecordCount
UST.MoveFirst
For ustRecCount = 1 To ustAnzrecs 'counter shows
all turnovers
'If ustRecCount > 200 And ustRecCount < 290 Then 'And
ustRecCount < 700
' MsgBox ustRecCount 'T005207001
' MsgBox UST.Fields(16)
'End If
If Left$(UST.Fields(16), 1) <> " " Then
'**IsNull(UST.Fields(16))
If UST.Updatable Then
UMSART = UST!ART 'Article number
from turnover (Umsätze table)
Descr = UST!descrizione 'and description
'Avoid:Data Type Mismatch Solution: "[WorkOrderID] = " &
Chr(34) & Worder & Chr(34) & ";"
SQLSelect = "SELECT ARTICLE.ART FROM ARTICLE in
'D:\Customersdb\Data14.mdb' " & _
"WHERE ARTicle.ART = " & Chr(34) & UMSART & Chr(34) & "
"
'found in ms-access
users group
Set ART = db.OpenRecordset(SQLSelect, dbOpenForwardOnly)
'dbOpenForwardOnly
If Not ART.EOF Then 'found
ARTART = ART!ART 'get Article number
from Article/Nop-for debug
Else
If ART.Updatable Then 'not found-new record
ART.AddNew
ART!ART = UMSART
ART!descrizione = Descr
ART.Update
End If
End If
ART.Close
Set ART = Nothing
UST.MoveNext
End If
End If
Next '*** For read Loop Umsätze /
Turnover

UST.Close
Set UST = Nothing
--- cut ---

still poor code, Lyle?

best regards, Helmut

[HelmutMunich]

Hello, this is helmut again.

I got more Information about "Data loss".
My turnover data come from a AS400. They are sent to Excel, and I
append these lines as recods
to my turnover table. Article number is defined as text. Nu duplicate
keys.
After the first Article number like "T001002003 " with
apparently appended blanks - or
were they Hex Values, before I got them in Excel, no article number is
displayed, when I
want to see them in a Message Box.
Which type of data is really exported by a AS400 ?

Still testing

Helmut

[HelmutMunich]

Its OK, I closed after the SQL - found, reopened again and added the
new record. But I cleaned the Turnover file from the appended blanks in
the Article number, which caused the article numbers to disappear from
reading the first time, when an Article number with these appended
blanks should be displayed. Thanks.

[David W. Fenton]

"HelmutMunich" <he**************@12move.de> wrote in
news:11**********************@g44g2000cwa.googlegr oups.com:

now I compacted and repaired, backend and frontend, changed the
users rights, avoided to run the password manager - which caused
a lot of trouble, until I fond out - in this user group that I
will never run this dangerous part.

Then I tried to change my statements, started the test and: since
turnover record number 208 I could not see any Art numbers any
more.

Actual code:

I don't know what your code is trying to accomplish and after a
basic scan, it looks to me that it still has all the problems I
mentioned in my previous message.

In short, the problem is not with corrupted data, it's not with
security, it's not with any configuration issues -- the problem is
in your actual code, which isn't doing what you think it's doing.

I can't even tell what that is, since you've not described it in any
detail.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[HelmutMunich]

Hello David,

thank you for your feedback. I went through the code, to make it more
comprehensible and then I found out, that the moveNext should go one
end if further. Thats how all article numbers, from the first blank on
seemed to be blank.
Now it works!

After all these new postings I did not imagine that I would hear
another response.

Many thanks, David!! :-)))

[David W. Fenton]

"HelmutMunich" <he**************@12move.de> wrote in
news:11*********************@g44g2000cwa.googlegro ups.com:

thank you for your feedback. I went through the code, to make it
more comprehensible and then I found out, that the moveNext should
go one end if further. Thats how all article numbers, from the
first blank on seemed to be blank.
Now it works!

After all these new postings I did not imagine that I would hear
another response.

I'm glad my response, obtuse as it may seem, helped.

I still think you're doing sequentially what ought to be done with
SQL update statements, but I never fully comprehended what you were
trying to accomplish, as I never understood what the problem space
was.

I'm just glad you solved the problem!

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc