Question Guidelines on building a db from Solid Edge files

C

Chronik

New member
Local time
Today, 17:22
Joined
Jun 14, 2012
Messages
8
Hi,

We are using thousands of Solid Edge parts daily and I would like to create a database that we could use to update some file properties as well as search for existing file instead of creating duplicates.

As of right now, I have a VB program that creates an excel table with the data I want. Now that i know the code works, i want to transfer it to Access.

What I would like to do:
- Have a DB that is updated hourly (or at least once a day)
- Create a userform that will be used to search within the DB.
- When using the userform, I want to be able to edit fields and that the file properties reflect those changes.
- I want the DB/search to be quick, there will be ~10000 entries. (Those 10000 entries could be seperated into ~10 groups/tables as there are ~10 part groups. This would allow the search to go through one or two tables instead of the 10000 entries when searching)

Code-wise, I think I will be able to handle all the post-programming (search, modify, etc). What i need to know is how to setup a table with the data and how to link it. (What kind of db do you suggest me, etc..)

I would really appreciate if someone could show me the way on how to start this project, as I never configured a DB from scratch before.

Thank you very much!
 
Last edited:
Here is the code I am using in VB/excel. I would like this code to be "translated" into VB/Access so that Access as a direct link to the files, instead of going through Excel, allowing me to edit the files properties easily.

Code: 
Sub GetFileProps()
    
    Sheets("Sheet1").Range("A2:M20000").Value = ""
    
    Dim strFile As String
    Dim strPath As String
    Dim FilePath As String
    Dim colFiles As New Collection
    Dim i As Integer
   
    strPath = "C:\TEST\"
    strFile = Dir(strPath)
   
   
    Dim objPropSets As PropertySets
    Dim objProps As Properties
    Dim objProp As Property
    Dim objPropIDs As 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)
            
            Sheets("Sheet1").Range("A" & x).Value = colFiles(i)
            
            Set objProps = objPropSets.Item("SummaryInformation")
            Sheets("Sheet1").Range("B" & x).Value = objProps.Item("Title")
            Sheets("Sheet1").Range("C" & x).Value = objProps.Item("Subject")
            Sheets("Sheet1").Range("H" & x).Value = objProps.Item("Keywords")
            Sheets("Sheet1").Range("L" & x).Value = objProps.Item("Author")
            Sheets("Sheet1").Range("M" & x).Value = objProps.Item("Last Author")
            
            Set objProps = objPropSets.Item("DocumentSummaryInformation")
            Sheets("Sheet1").Range("G" & x).Value = objProps.Item("Category")
            
            Set objProps = objPropSets.Item("MechanicalModeling")
            Sheets("Sheet1").Range("D" & x).Value = objProps.Item("Material")
            
            
            Set objProps = objPropSets.Item("Custom")
            On Error GoTo Handler
            Sheets("Sheet1").Range("E" & x).Value = objProps.Item("largeur")
            Sheets("Sheet1").Range("F" & x).Value = objProps.Item("longueur")
            On Error GoTo 0
            
            Set objProps = objPropSets.Item("ProjectInformation")
            Sheets("Sheet1").Range("I" & x).Value = objProps.Item("Document Number")
            Sheets("Sheet1").Range("K" & x).Value = objProps.Item("Revision")
            Sheets("Sheet1").Range("J" & x).Value = objProps.Item("Project Name")
            
            Call objPropSets.Close
            
            x = x + 1
        Next i
    End If
    c
    Sheets("Sheet1").Cells.Columns.AutoFit
    Sheets("Sheet1").Range("A1").Select
    
    Exit Sub

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

End Sub
 
Have a DB that is updated hourly (or at least once a day)
Click to expand...
How? Where is the data coming from and why don't you link to it so you don't have to keep updating the Access tables?
 
I can't have the DB updated in real time. The data comes from solid edge files created by users. It would be ok to run an DB update only once every couple of hours, or even once every day.

Here is the code i have right now.

It seems that Access doesn't like the way the Solid Edge File Properties lib works. I get errors in the handler :

Code: 
objProps.Name = "Custom"
--> "Method or data member not found" error.

and i get errors at the first line of code:

Code: 
Set objProps = objPropSets.Item("SummaryInformation")
--> "Type mismatch" error.

Here is the full code :

Code: 
Option Compare Database
Public Sub UpdateTable()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    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 As Integer
   
    strPath = "C:\TEST\"
    strFile = Dir(strPath)
   
    Dim objPropSets As PropertySets
    Dim objProps As Properties
    Dim objProp As Property
    Dim objPropIDs As 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)
            
            rs.AddNew
            
            rs![File Name] = colFiles(i)
                        
            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
            
        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 don't understand why i'm getting all those errors while they worked fine in excel/vba.

Thanks!
 
I suspect that you haven't referenced the library.

Check that by Code Window->Debug->Compile

If it complains about various SolidEdge bits, then

Code window->Tools->References

and tick the SolidEdge thing (Look in your Excel in the same place to see the name of the reference)
 
As I said, it works flawlessly in Excel/VBA. In both Excel and Access i've include library "Solid Edge File properties".

Here is the working excel code :
Code: 
Sub GetFileProps()
    
    Sheets("Sheet1").Range("A2:M20000").Value = ""
    
    Dim strFile As String
    Dim strPath As String
    Dim FilePath As String
    Dim colFiles As New Collection
    Dim i As Integer
   
    strPath = "C:\TEST\"
    strFile = Dir(strPath)
   
   
    Dim objPropSets As PropertySets
    Dim objProps As Properties
    Dim objProp As Property
    Dim objPropIDs As 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)
            
            Sheets("Sheet1").Range("A" & x).Value = colFiles(i)
            
            Set objProps = objPropSets.Item("SummaryInformation")
            Sheets("Sheet1").Range("B" & x).Value = objProps.Item("Title")
            Sheets("Sheet1").Range("C" & x).Value = objProps.Item("Subject")
            Sheets("Sheet1").Range("H" & x).Value = objProps.Item("Keywords")
            Sheets("Sheet1").Range("L" & x).Value = objProps.Item("Author")
            Sheets("Sheet1").Range("M" & x).Value = objProps.Item("Last Author")
            
            Set objProps = objPropSets.Item("DocumentSummaryInformation")
            Sheets("Sheet1").Range("G" & x).Value = objProps.Item("Category")
            
            Set objProps = objPropSets.Item("MechanicalModeling")
            Sheets("Sheet1").Range("D" & x).Value = objProps.Item("Material")
            
            
            Set objProps = objPropSets.Item("Custom")
            On Error GoTo Handler
            Sheets("Sheet1").Range("E" & x).Value = objProps.Item("largeur")
            Sheets("Sheet1").Range("F" & x).Value = objProps.Item("longueur")
            On Error GoTo 0
            
            Set objProps = objPropSets.Item("ProjectInformation")
            Sheets("Sheet1").Range("I" & x).Value = objProps.Item("Document Number")
            Sheets("Sheet1").Range("K" & x).Value = objProps.Item("Revision")
            Sheets("Sheet1").Range("J" & x).Value = objProps.Item("Project Name")
            
            Call objPropSets.Close
            
            x = x + 1
        Next i
    End If
    
    Sheets("Sheet1").Cells.Columns.AutoFit
    Sheets("Sheet1").Range("A1").Select
    
    Exit Sub

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

End Sub
And here is the non working Access Code:

Code: 
Option Compare Database
Public Sub UpdateTable()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    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 As Integer
   
    strPath = "C:\TEST\"
    strFile = Dir(strPath)
   
    Dim objPropSets As PropertySets
    Dim objProps As Properties
    Dim objProp As Property
    Dim objPropIDs As 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)
            
            rs.AddNew
            
            rs![File Name] = colFiles(i)
                                        
            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
            
        Next i
    End If
    
    Exit Sub

Handler:
    If Err.Description = "Subscript out of range" Then 'And objProps.Name = "Custom" Then
        Resume Next
    End If
    
End Sub
When troubleshooting the code going line by line, should I see a new line being added and the file name entered when the code goes through :
Code: 
            rs.AddNew
            rs![File Name] = colFiles(i)
Or does it appears only once it reaches
Code: 
rs.Update
??

I can't post a picture but refs in Excel and Access are the same. And i tried to add a rs.update right after the rs![File Name] = colfiles(i) and i don't see any data being entered in the db. is this normal?

Thanks
 

Attachments

  • Refs.jpg
    Refs.jpg
    74.1 KB · Views: 144
Your post is confusing. What happend to the mismatch errors from the previous post?

"Nonworking" doesn't mean anything to anyone but you. What is the specific problem now? Always say what you did, what happend, and what you expected to happen. And any error messages and location of the line that generated it.

And "should I see a new line being added " where? The table isn't updated until .Update is called.
 
spikepl said:
Your post is confusing. What happend to the mismatch errors from the previous post?

"Nonworking" doesn't mean anything to anyone but you. What is the specific problem now? Always say what you did, what happend, and what you expected to happen. And any error messages and location of the line that generated it.

And "should I see a new line being added " where? The table isn't updated until .Update is called.
Click to expand...

I skipped the "Method or data member not found" error (by removing the ObjProp part of the handler code, see post #6, 2nd code box, at the bottom I added a " ' "). It seems there really is a problem with the library even though it is activated the same way as in Excel. As soon as it reaches
Code: 
            Set objProps = objPropSets.Item("SummaryInformation")
it crashes (Mismatch error).

As for the "new line thing", i found out that if I manually click on "Refresh All" after this code :
Code: 
    If colFiles.Count > 0 Then
        For i = 1 To colFiles.Count
        
            FilePath = strPath & colFiles(i)
            
            Call objPropSets.Open(FilePath)
            rs.AddNew
            rs![File Name] = colFiles(i)
            rs![Title] = "asdfasdf"
            
            rs.Update
I see new entries in the DB. I have no idea why i have to manually click on the Refresh button though, i thought that rs.update was performing a refresh..?!
 
You are now running a different application - Access. My guess is that the types are ambiguous in Access.

Try

Code: 
Dim objPropSets As Object     
Dim objProps As Object     
Dim objProp As Object     
Dim objPropIDs As Object
or start typing -SLOWLY

Code: 
Dim objPropSets As FileEdg...
and see what Intellisense offers of choices. You might wind up with something like

Code: 
 Dim objPropSets As FileEdge.PropertySets
 
You were right, there was a problem using the dim functions because Properties and such were allocated to something else in access.

My code now kind of works, the only problem is that I have no clue on how to use the movefirst/movelast commands, therefore the database is not being edited correctly. I tried throwing movelast/movefirst anywhere in the code to see how it responds without any conclusive results.

Code: 
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
 
So you are opening a recordset, checking if a record exists, and if it does you want to update the record. But you did not tell access which record but you just edit the first one.

Throw out the Dcount thing - it has no business here.

Look up in the documentation the DAO method .FindFirst. It will move the pointer to the desired record. Or else the recordset property .NoMatch will be true. Look it up.
 
Thanks for the tip.

I tried with the following code, but keep getting 3251 error. ("operation is not supported for this type of object")
Code: 
Option Compare Database

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 "[Test1] = '*1234*'"
        
        If NoMatch Then
            .AddNew
            '[Add routine here]
        Else
            .Edit
            '[Edit routine here]
        End If
    End With
End Sub
Some people say it has to be a dynaset or a snapshot rs, but this guy's code is the same as mine and he says that it works. (I can't post a link since i have fewer than 10 posts, but its post ID 1638583 on dbforums dot com)

Thanks
 
spikepl said:
Thanks for bringing it to my attention that you have crossposted.

Read this [...] do what is required.
Click to expand...

I'm not even a member n the forum I just mentionned. But it is indeed a crosspost with another US forum. I posted here at first then realized it was a UK, so i posted in a US forum but when i came to delete the post here someone had already replied.

I will make sure I update both threads appropriately and close them afterwards.
 
You must log in or register to reply here.

Similar threads

The_Doc_Man
Important Security Guidelines
2
Replies
24
Views
727
The_Doc_Man
The_Doc_Man
P
Unknown DB
Replies
9
Views
575
pdanes
P
P
Solved Imported DB to windows 11 does not show objects or top panel
2
Replies
23
Views
401
Gasman
Gasman
M
Solved Issue with Edge Browser Control on User's Computer
Replies
7
Views
1,383
Mohsin Malik
M
E
BAT File Command Help
2
Replies
26
Views
652
Isaac
Isaac

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom