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 :
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.
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 :
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.