Results 1 to 8 of 8

Thread: Weird VBA behaviour writting DBASE tables

  1. #1
    Seeker Atlas's Avatar
    Join Date
    Jan 2019
    Posts
    5
    Articles
    0
    Excel Version
    2007-2016

    Weird VBA behaviour writting DBASE tables



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Seeker Atlas's Avatar
    Join Date
    Jan 2019
    Posts
    5
    Articles
    0
    Excel Version
    2007-2016
    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"))

    Click image for larger version. 

Name:	EXCEL VBA REFERENCES.png 
Views:	8 
Size:	10.3 KB 
ID:	8777

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

    Thanks in advance

  3. #3
    Conjurer Kenneth Hobson's Avatar
    Join Date
    Mar 2014
    Location
    Tecumseh, OK
    Posts
    123
    Articles
    0
    Excel Version
    365
    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

  4. #4
    Seeker Atlas's Avatar
    Join Date
    Jan 2019
    Posts
    5
    Articles
    0
    Excel Version
    2007-2016
    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?

  5. #5
    Conjurer Kenneth Hobson's Avatar
    Join Date
    Mar 2014
    Location
    Tecumseh, OK
    Posts
    123
    Articles
    0
    Excel Version
    365
    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

  6. #6
    Seeker Atlas's Avatar
    Join Date
    Jan 2019
    Posts
    5
    Articles
    0
    Excel Version
    2007-2016
    Quote Originally Posted by Kenneth Hobson View Post
    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?

  7. #7
    Seeker Atlas's Avatar
    Join Date
    Jan 2019
    Posts
    5
    Articles
    0
    Excel Version
    2007-2016
    I forgot to say that when using a local drive, it runs as expected (new records are added each time).

    Have a nice day,

  8. #8
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,573
    Articles
    0
    Excel Version
    2010 on Xubuntu
    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.
    Thank you Ken for this secure forum.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •