Guidelines on building a db from Solid Edge files

[help_me_with_access]

doesn't make sense bud. step through with F8. you've got different goto redirects in there. anything could be happening. but acc DOES update tables with the right coding. that's not the problem.

[Chronik]

Only goto i have is for the err handler which is a skip error function when reading the some custom properties that files might/might not have.

In the TEST Folder there are 50 files. C100 is the first one. When i run the program with F8, after rs.update on the "first file" which is C100, if i manually Refresh Table1, C125's title changes to "Blablablablabla" which is C100's title. Once the code processes C125, (i still have to manually click reset even though there is a rs.update at the end of every loop), C125's title goes back to what it is supposed to be.

I really don't get this one.

[help_me_with_access]

well I don't do debug work without a contract.

I'm sure you'll figure it out quick. the routine is not complex...and you're a smart guy!

[Chronik]

Ok just did my homework a bit on this one.

File C125 changes properties every loop. It is the only one that does it. For instance, when reading C100, C125 will be updated with C100 properties, when reading C101, C125 will be updated with C101 properties, etc.

I am not familiar enough with Access to know why it behaves this way, any clues?

[help_me_with_access]

ha ha...it just clicked...my pcode was the problem. more or less because I either assumed you could take the reigns yourself or I was just too naive and forgot that many here are not experts.

at the end of every loop, are you missing a "rs.movenext" if you're editing the record? that would do it. by the way, another thing also comes to mind here. if you ARE going to combine EDIT and ADDNEW, you're probably going to have to use MOVEFIRST and MOVE as well. because I don't know where the pointer is after an ADDNEW is carried out. if it ends up at the end of the dataset, you have to movefirst and then move (x) to get back to where you were. put the absolutepos in a var to remember where you were.

i believe bookmarks also work doing this stuff, but i've never used them. I don't believe in them because you're trusting acc to work for you when you do that.

you can find your record num though. use rs.absolution position to find in when you're debugging. the property is base 0. e.g. - record #1 = 0.

[Chronik]

ha ha...it just clicked...my pcode was the problem. more or less because I either assumed you could take the reigns yourself or I was just too naive and forgot that many here are not experts.

at the end of every loop, are you missing a "rs.movenext" if you're editing the record? that would do it. by the way, another thing also comes to mind here. if you ARE going to combine EDIT and ADDNEW, you're probably going to have to use MOVEFIRST and MOVE as well. because I don't know where the pointer is after an ADDNEW is carried out. if it ends up at the end of the dataset, you have to movefirst and then move (x) to get back to where you were. put the absolutepos in a var to remember where you were.

i believe bookmarks also work doing this stuff, but i've never used them. I don't believe in them because you're trusting acc to work for you when you do that.

you can find your record num though. use rs.absolution position to find in when you're debugging. the property is base 0. e.g. - record #1 = 0.

Hehe that's a big piece of information i was missing. I tried this morning to work with movefirst but end up not using it.

So if i get it right, when editing, I have to find where I am, then movefirst the data and then move back to where i was?

And whats with the addnew (? how does that whole data entry works anyways? It filters the data alphabetically everytime there is a new entry and creates a "row" where it belongs?)

Heres what i just tried, its not working hehe!

Option Compare Database
Public Sub UpdateTable()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Dim Edit As Boolean
    Set rs = Nothing

    Set db = CurrentDb
    Set rs = db.OpenRecordset("Table1")
    
    Dim strFile As String
    Dim strPath As String
    Dim FilePath As String
    Dim colFiles As New Collection
    Dim i, RS_Pos As Integer
   
    strPath = "C:\TEST\"
    strFile = Dir(strPath)
   
    Dim objPropSets As SolidEdgeFileProperties.PropertySets
    Dim objProps As SolidEdgeFileProperties.Properties
    Dim objProp As SolidEdgeFileProperties.Property
    Dim objPropIDs As SolidEdgeFileProperties.PropertyIDs
    Set objPropSets = CreateObject("SolidEdge.FileProperties")
    Dim x As Integer
    x = 2
    
    While strFile <> ""
        If Right(strFile, 3) = "psm" Or Right(strFile, 3) = "par" Then
            colFiles.Add strFile
        End If
        strFile = Dir
    Wend

    If colFiles.Count > 0 Then
        For i = 1 To colFiles.Count
        
            FilePath = strPath & colFiles(i)
            
            Call objPropSets.Open(FilePath)
            
            If Not IsNull(DCount("[File Name]", "[Table1]", "[File Name]='" & colFiles(i) & "'")) Then
                rs.Edit
                Edit = True
                RS_Pos = rs.AbsolutePosition
                rs.MoveFirst
                
            Else
                rs.AddNew
                rs![File Name] = colFiles(i)
            End If
            

                                      
            Set objProps = objPropSets.Item("SummaryInformation")
            rs![Title] = objProps.Item("Title")
            rs![Subject] = objProps.Item("Subject")
            rs![Keywords] = objProps.Item("Keywords")
            rs![Author] = objProps.Item("Author")
            rs![Last Author] = objProps.Item("Last Author")
            
            Set objProps = objPropSets.Item("DocumentSummaryInformation")
            rs![Category] = objProps.Item("Category")
            
            Set objProps = objPropSets.Item("MechanicalModeling")
            rs![Material] = objProps.Item("Material")
            
            Set objProps = objPropSets.Item("Custom")
            On Error GoTo Handler
            rs![Largeur] = objProps.Item("largeur")
            rs![Longueur] = objProps.Item("longeur")
            On Error GoTo 0
            
            Set objProps = objPropSets.Item("ProjectInformation")
            rs![Document Number] = objProps.Item("Document Number")
            rs![Revision] = objProps.Item("Revision")
            rs![Project Name] = objProps.Item("Project Name")
            
            Call objPropSets.Close
            rs.Update
            x = x + 1
            
            If Edit = True Then
                rs.Move (RS_Pos)
                rs.MoveNext
                Edit = False
            End If
            
        Next i
    End If
    
    Exit Sub

Handler:
    If Err.Description = "Subscript out of range" And objProps.Name = "Custom" Then
        Resume Next
    End If
    
End Sub

[help_me_with_access]

"its not working" isn't very helpful, is it?

I am actually working today, but since you seem to be a developer with something on the ball (), attached is how I would do what you're doing. and oddly enough, the file is one that I use for testing various things...

look at the module "example".

[Chronik]

Haha thanks!

I said "it doesnt work" because i threw rs.movefirst rs.move anywhere without really knowing what the point of using them.

In your example, why are you using rs.movelast rs.movefirst back to back?

Why do I have to bother with the position of the entry, what's Access' logic behind all this?

[help_me_with_access]

back-to-back answer: the answer is because in visual basic, only 1 record in a dataset is visible to the code unless you move to the last record. stupid? YES. mandatory? YES. at least I believe so. but theorists will tell you otherwise I'm sure!

what's the logic? not sure I understand what you're saying. a program's logic is what you're stuck with. in this case, it's 'C', which is what access is written in. if you want to know why the program does what it does and why it does what it does, contact the ms engineer that designed it. =)

[Chronik]

back-to-back answer: the answer is because in visual basic, only 1 record in a dataset is visible to the code unless you move to the last record. stupid? YES. mandatory? YES. at least I believe so. but theorists will tell you otherwise I'm sure!

what's the logic? not sure I understand what you're saying. a program's logic is what you're stuck with. in this case, it's 'C', which is what access is written in. if you want to know why the program does what it does and why it does what it does, contact the ms engineer that designed it. =)

I understand, but not to the point where I am able to make that thing work using those movefirst/movelast function. I tried inserting

rs.movelast
rs.movefirst

at different location in my code and I couldn't find the correct way to use it. Most of the time i was getting "update without edit or add" error.

I think i'll understand how it works when i'll see it working, now i just really don't get with C125 is updated all the time even when throwing some movefirst/last in the code without any resulting changes.

Thanks!

[help_me_with_access]

"update without edit or add" error.

that can occur such that the error is literal, or it could be a consequence of incomplete blocking of code. e.g. - no END WITH statement after WITH.

this thread is a little too long thought at this point I think...I'll make you deal: how about I fix the visual basic code for you and you take care of the solid edge stuff. OK?

post the code and I'll fix the vb. I don't have time to mess with the solid edge stuff, as I'm not even sure what it is anyway. sorry!

hope ya had a good weekend!

[Chronik]

"update without edit or add" error.

that can occur such that the error is literal, or it could be a consequence of incomplete blocking of code. e.g. - no END WITH statement after WITH.

this thread is a little too long thought at this point I think...I'll make you deal: how about I fix the visual basic code for you and you take care of the solid edge stuff. OK?

post the code and I'll fix the vb. I don't have time to mess with the solid edge stuff, as I'm not even sure what it is anyway. sorry!

hope ya had a good weekend!

Yup not bad week-end, finished installing my 8 ceiling speakers, so I finally cleaned the house a bit and slept in a drywall-dust-free environment hehe! hope you had a good w-end as well!

Here is the code as of now.

Option Compare Database
Public Sub UpdateTable()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Dim Edit As Boolean
    Set rs = Nothing

    Set db = CurrentDb
    Set rs = db.OpenRecordset("Table1")
    
    Dim strFile As String
    Dim strPath As String
    Dim FilePath As String
    Dim colFiles As New Collection
    Dim i, RS_Pos As Integer
   
    strPath = "C:\TEST\"
    strFile = Dir(strPath)
   
    Dim objPropSets As SolidEdgeFileProperties.PropertySets
    Dim objProps As SolidEdgeFileProperties.Properties
    Dim objProp As SolidEdgeFileProperties.Property
    Dim objPropIDs As SolidEdgeFileProperties.PropertyIDs
    Set objPropSets = CreateObject("SolidEdge.FileProperties")
    Dim x As Integer
    x = 2
    
    While strFile <> ""
        If Right(strFile, 3) = "psm" Or Right(strFile, 3) = "par" Then
            colFiles.Add strFile
        End If
        strFile = Dir
    Wend

    If colFiles.Count > 0 Then
        For i = 1 To colFiles.Count
            
            FilePath = strPath & colFiles(i)
            
            Call objPropSets.Open(FilePath)

            If Not IsNull(DCount("[File Name]", "[Table1]", "[File Name]='" & colFiles(i) & "'")) Then
                rs.Edit
                Edit = True

            Else
                rs.AddNew
                rs![File Name] = colFiles(i)
            End If
                             
            Set objProps = objPropSets.Item("SummaryInformation")
            rs![Title] = objProps.Item("Title")
            rs![Subject] = objProps.Item("Subject")
            rs![Keywords] = objProps.Item("Keywords")
            rs![Author] = objProps.Item("Author")
            rs![Last Author] = objProps.Item("Last Author")
            
            Set objProps = objPropSets.Item("DocumentSummaryInformation")
            rs![Category] = objProps.Item("Category")
            
            Set objProps = objPropSets.Item("MechanicalModeling")
            rs![Material] = objProps.Item("Material")
            
            Set objProps = objPropSets.Item("Custom")
            On Error GoTo Handler
            rs![Largeur] = objProps.Item("largeur")
            rs![Longueur] = objProps.Item("longeur")
            On Error GoTo 0
            
            Set objProps = objPropSets.Item("ProjectInformation")
            rs![Document Number] = objProps.Item("Document Number")
            rs![Revision] = objProps.Item("Revision")
            rs![Project Name] = objProps.Item("Project Name")
                        
            Call objPropSets.Close
            rs.Update
            x = x + 1
            
            If Edit = True Then
                'rs.MoveNext
                Edit = False
            End If
            
        Next i
    End If
    
    Exit Sub

Handler:
    If Err.Description = "Subscript out of range" And objProps.Name = "Custom" Then
        Resume Next
    End If
    
End Sub

I've read on the subject a bit and I was thinking of using the findfirst function instead of the Dcount, but i can't figure out how it works. Some people declare the recordsets exactly like I do and say it works, while some other people say it has to be a dynaset or a snapshop recordset... I tried with the following code and no results : (getting error 3251 at the findfirst row "operation is not support for this type of object" )

Sub UpdateTable()

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Table1")

    Dim temp As String
    temp = "FP-C325.psm"
    
    With rst
        .FindFirst "[File Name] = '" & temp & "'"
        
        If NoMatch Then
            .AddNew
            '[Add routine here]
        Else
            .Edit
            '[Edit routine here]
        End If
    End With
End Sub

Would the 2nd method be more efficient ?(if i manage to make it work)

Thanks!

[help_me_with_access]

effeciency is NOT an issue with this code, as there isn't any of it. if you've got people complaining about effeciency with THIS code, they've got serious issues! let us see now...is there a difference between .5 seconds and 1 second of processing time? hmmmm....

and actually, even funnier, on CNN today there was an interview with a chick that was associating obesity with energy consumption. the heavier you are apparently, the more you contribute to the over-consumption of energy in the world? hmmm...well I'm a bodybuilder and I'm well over 200 lbs myself. So does that mean that I have to drop by passion because the corporate world makes mistakes? here's my answer.... NO. I just thought the article was funny, not to mention off-the-wall ridiculous.

by the way, findfirst() works fine, but my observation of this function is just one in visual basic that is inconsistent. e.g. - there's no consistency in the syntax that is required under various scenarios. don't ask why...the answer always is the same!

I'll try to re-post some of my own "hot fixes" later on tonight. if you haven't gotten it debugged yourself by then...

[Chronik]

Haha i guess you're right. I was just asking. It's just that the 2nd one looked neat, but i do not care. I guess i could try both if i manage to make them work. I'll see how it goes with 10000 files to process.

Thanks for your help, again. I'll try to make it work during my free time this afternoon. Have a nice day.

[help_me_with_access]

I'll see how it goes with 10000 files to process.

well THAT is a little different!