Weird VBA behaviour writting DBASE tables

Atlas

New member
Joined
Jan 10, 2019
Messages
5
Reaction score
0
Points
0
Excel Version(s)
2007-2016
Hi there,
I’m an Excel passionate and I always try to learn something. Even so, I’d say I know less than 5% of Excel features.

I’m stuck with a weird trouble.
I have a lot of vba scripting in order to work with DBF files (my ERP is Foxpro 2.5 ) so I use Excel as a front end app.
One of the last PC I acquired is running Office 2016 32 b (OS Windows 10 64b)
The thing is a workbook (Excel xlsm) is dealing with goods we receive, so the user has a few fields to fill.
Once they’re filled Save button must be pushed and then the code is:
Set rS = New ADODB.Recordset
Set cnnBASE = New ADODB.Connection
strCon = "driver={Microsoft dBase Driver (*.dbf)};"
strCon = strCon & "driverid=277;dbq=" & Range("PathGes")
cnnBASE.Open strCon


' If Range("rFD").Value = 0 Then
sqlCLI = "SELECT * FROM FULLDES WHERE FNUM>0 ORDER BY FNUM DESC"
' Else
' sqlCLI = "SELECT * FROM FULLDES WHERE FNUM=" & vFD
' End If
Set rS = New ADODB.Recordset
rS.Open sqlCLI, cnnBASE, adOpenDynamic, adLockOptimistic
If True Then
If rS.EOF() Then ' if file is empty sets Num = 1
rFD.Value = 1
Else
If Range("rFD") = 0 Then
rS.MoveFirst
Range("rFD") = rS!FNUM + 1 'assigned
End If
End If
vFD = Range("rFD")


'********************************
' Write REGISTRE to dbf FULLDES
rS.AddNew
rS!FNUM = rFD.Value
rS!Data = Range("rData") ' DATA
rS!Provee = Range("rProve") ' suplier code
rS!PROCE = Left(Range("rProce"), 20) ' from
rS!TRANSP = Left(Range("rTransp"), 25) ' TRANSPORT
rS!MATRI = Left(Range("rMatri"), 10) ' plaque
rS!NLIN = WorksheetFunction.MaRange("rLIN"))
rS!OBSER = Left(Range("rObser"), 50) ' Notes
rS!IOOK = Range("rOKOrg")
rS!IONO = Range("rNotOKOrg")
rS!IOPR = Range("rPresa")
rS!NCONF = Range("rNC")
rS!NNC = Left(Range("rNNC"), 10)
rS!DPOK = Range("rDPOK")
rS!DPRE = Range("rRefus")
rS!REGIS = Range("rREGIS")

rS.Update
rS.Close
End If

'********************************
' Write line records

' If vFD = 0 Then
' ' nothing
' Else
' ' if lines are found, set them to negative value
' sqlCLI = "UPDATE FDEXT SET FNUM=-FNUM WHERE FNUM=" & rFD
' cnnBASE.Execute sqlCLI, adCmdText
' End If
Set rS = New ADODB.Recordset
sqlCLI = "Select * FROM FDEXT"
rS.Open sqlCLI, cnnBASE, adOpenDynamic, adLockOptimistic
If True Then 'Not rS.EOF() Then
For cI = 1 To 24
sCampA = "A" & cI
sCampQ = "Q" & cI
If Range("riCodis").Offset(cI, 0) > " " Then
'codX = fTexACod(rFD.Offset(0, cI + 5))
rS.AddNew
rS!FNUM = rFD
rS!Data = Range("rData") ' DATA
rS!LIN = cI
rS!A1 = Range("riCodis").Offset(cI, 0)
rS!D1 = Range("riCodis").Offset(cI, 1)
rS!Q1 = Range("riCodis").Offset(cI, 2)
rS.Update
End If
Next
End If
Surt:
If rS.State = 1 Then
rS.Close
End If
cnnBASE.Close

The point is:
With all PC’s this is working fine (since many years) (up to Excel 2013 for sure)
With this PC running Excel 2016:
- Open the table with no trouble
- Read contents
- I can .addnew without troubles (theoretically, debugging no problem detected)
- But really DOESN’T WRITE new records (rs.Addnew ) nor give any error. (most of the times)
- If debugging everything is fine (although a few times really write to the table, most of them do not)
- Once, at least, has written a new record to one of the two tables, not to the other.

I have these VBA references :
image002.png@01D49F6B.D936A0A0



Can anyone figure out what could it be? I’m really lost… I can’t understand why it doesn’t crash but no data is modified, as it’s coded.
I’d appreciate your help, directly or after your network knowledge…

Thanks in advance.
 
Hi again,
I forgot to post the Vba references I've activated, and I saw there's an error in line:
rS!NLIN = WorksheetFunction.MaRange("rLIN"))
should be
rS!NLIN = WorksheetFunction.MaxRange("rLIN"))

EXCEL VBA REFERENCES.png

I'd appreciate any ideas who put light in it.
Shoult it be Excel ? Should it be a network behaviour? ...

Thanks in advance
 
Welcome to the forum!

When pasting code, please paste between code tags. Click the # icon on the reply toolbar to insert the tags.

Attaching a simple file is another way to get help. It would include the code and the references.

I don't know that the DAO reference is needed. It probably doesn't hurt anything. Try runs when you know that no one has the DBF file open and when someone does. Sometimes, the lock part needs a tweak. adOpenDynamic, adLockOptimistic
 
Thanks for your answer, Kenneth.
The problem is always with (just) this PC, no matters if no one else is using the DBF. (and
adOpenDynamic, adLockOptimistic is what I had coded already)
What drives me nuts is why the code doesn't give any error if no data is written ( the code goes throught open connection , open
two tables,
.addnew record to each one, update each one (first one, the the other), close recordsets and close connection. But you check the result and the tables were last modified (say) yesterday, not now after running the code.
It looks to me it could be a problem with the net card or net protocol, but I don't know where to look at.
Any ideas there?
 
Might be some king of permissions issue I guess.

I don't know if the path to the dbf file has a private user path. Since you say network, then permissions may be an issue...

I guess you know that the If True will always be True so Else will not ever execute?
Code:
Sub Test()  
 If True Then
    MsgBox True
    Else
    MsgBox False
  End If
End Sub
 
Might be some king of permissions issue I guess.

I don't know if the path to the dbf file has a private user path. Since you say network, then permissions may be an issue...

I guess you know that the If True will always be True so Else will not ever execute?
Code:
Sub Test()  
 If True Then
    MsgBox True
    Else
    MsgBox False
  End If
End Sub

Oh yes, I knew. I coded this 'True' after having the problems I explained (and I left it there). Still, if rs.EOF() were True (or Not rs.EOF() where False) I should receive an error when trying to .addnew record. Wouldn't I?
 
I forgot to say that when using a local drive, it runs as expected (new records are added each time).

Have a nice day,
 
Please don't quote whole posts -- it's just clutter and makes the thread hard to read. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding
For normal conversational replies, try using the QUICK REPLY box below.
 
Back
Top