Guidelines on building a db from Solid Edge files

[Chronik]

Hi,



I'm new to access but I am pretty good with excel/vba. I want to build a database with the file properties of our solid edge parts. They are classified in different folders and subfolders, so lets say there are 10000 in 10 folders so you get an idea of what i'm talking about.

I want to create a userform that I will distribute on several workstations that will be linked to a database that will contain those " 10 " tables so that people use the form to find existing files instead of creating new parts everytime.

I also want to be able to modify the properties from within the userform.

As of right now, i have a program that populates an excel worksheet with all the data i want. Here is the 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

The problem is that since I want to have a direct link between Access and the file in order to edit the file properties, I can't rely on excel to populate the data. Therefore i would like the previous code to populate a table in access and i can't figure out how to do it. Maybe i just need to find out what the equivalent of "Sheets("Sheet1").Range("") is in access, I don't know...

Thanks!

[help_me_with_access]

The problem is that since I want to have a direct link between Access and the file in order to edit the file properties, I can't rely on excel to populate the data. Therefore i would like the previous code to populate a table in access and i can't figure out how to do it.

well I'm not sure I understand that, my friend. first of all, populating a table in access is the exactly same code as it is in excel, except for establishing your recordset objects and looping it.

what do you mean by DIRECT LINK? so you want to edit the file properties? well that has nothing to do with the acc program. that's a visual basic operation isn't it?

[Chronik]

well I'm not sure I understand that, my friend. first of all, populating a table in access is the exactly same code as it is in excel, except for establishing your recordset objects and looping it.

what do you mean by DIRECT LINK? so you want to edit the file properties? well that has nothing to do with the acc program. that's a visual basic operation isn't it?

Yes, well I can't populate the table with

Sheets("Sheet1").Range("A" & x) = objProps.Item("Title")

That's my problem...

And regarding the edit, I was just saying that i want the link to be between the file and access without using excel, because if I do acces-excel-file i will have to re-update excel after every edits i make.

Thanks!

[help_me_with_access]

well then that's simple. here's some pcode. the pcode is in red, everything else is verbatim:

'get files with code

'real vba code for acc:
dim db as dao.database
dim rs as dao.recordset

set db = currentdb
set rs = db.openrecordset(tablename)

do until yourCondition
  rs.add
  rs![fieldName] = value
  ...
  ...
  ...
  rs.update
loop

or you can simply write an acc routine to import your excel data. transferSpreadsheet() is a good method to use.

[Chronik]

well then that's simple. here's some pcode. the pcode is in red, everything else is verbatim:

'get files with code

'real vba code for acc:
dim db as dao.database
dim rs as dao.recordset

set db = currentdb
set rs = db.openrecordset(tablename)

do until yourCondition
  rs.add
  rs![fieldName] = value
  ...
  ...
  ...
  rs.update
loop

or you can simply write an acc routine to import your excel data. transferSpreadsheet() is a good method to use.

I'd prefer to skip the excel and have access do all the work. I'll have one program that takes care of updating the db and another one that will be used on several workstations to perform the searches.

Here is what I did with what you told me previously. I created Table1 and added the fields. Then I wrote the code in Module 1.

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 receive an error from the handler, and when i skip it it crashes at Set rs = db.OpenRecordset(Table1)

Thanks

[help_me_with_access]

Table1 needs to be enclosed in DOUBLE quotes. visual basic is different than many web technologies, in that it does not see single quotes as symbols encapsulating string values. double quotes only.

[Chronik]

Table1 needs to be enclosed in DOUBLE quotes. visual basic is different than many web technologies, in that it does not see single quotes as symbols encapsulating string values. double quotes only.

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

objProps.Name = "Custom"

--> "Method or data member not found" error.

and i get errors at the first line of code:

Set objProps = objPropSets.Item("SummaryInformation")

--> "Type mismatch" error.

Here is the full 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!

[help_me_with_access]

Chronik,

to be honest with you, I assumed that you knew what you were doing with the SolidEdge stuff. I'm assuming you're calling up a library to work with them?

in every visual basic project you have to reference the windows libraries or other libraries you are going to use in order to get access to the elements within those libraries, for use in vb projects. Did you reference external libs in excel? if you did, you have to do the same thing in every office program. you have to tell the environment what libraries you need access to, otherwise it won't know.

is that the issue here I wonder?

[Chronik]

Chronik,

to be honest with you, I assumed that you knew what you were doing with the SolidEdge stuff. I'm assuming you're calling up a library to work with them?

in every visual basic project you have to reference the windows libraries or other libraries you are going to use in order to get access to the elements within those libraries, for use in vb projects. Did you reference external libs in excel? if you did, you have to do the same thing in every office program. you have to tell the environment what libraries you need access to, otherwise it won't know.

is that the issue here I wonder?

Hi,
Yes i do know what i'm doing with the Solid Edge stuff. 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 :

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:

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 :

            rs.AddNew
            rs![File Name] = colFiles(i)

Or does it appears only once it reaches

rs.Update

?

Thanks a lot for your help on this!

[Chronik]

Refs library in Access and Excel (Access left, excel right)


And I tried to isnert a rs.update after trying to input the file name in a field, it does nothing.



Thanks

[help_me_with_access]

you're welcome for the help.

visual basic lessons to learn here:

*to see result of code, break it AFTER the line executes. ON the line does nothing, as no execution happens there.
*records appear or the modifications appear in recordsets after they are updated only.

this:

objProps.Name = "Custom"

is referring to .NAME. it doesn't recognize the prop. hell, I wouldn't know why if that is really the case, because vba is one environment for all office programs. same syntax, same compiler, nothing changes so it's not like the lib is being read differently between xl and acc (I wouldn't think anyway!)

this:

Set objProps = objPropSets.Item("SummaryInformation")

may be running into confusion with DAO. DAO has elements named "property" and "properties". so I would assume acc is associating the code with DAO first. DAO is a default library in acc, whereas in xl it is not. I'm fairly sure that's the case with this one at least. or the other case may be that the default "database" objects referenced in the acc program are not there in xl, and so those are being looked at first by the compiler. DAO is used both in xl and acc, sorry! prefix those 2 decs with qualifier = lib name. e.g. -

dim objProps as LIBRARYNAME.property

do you agree?

[Chronik]

*to see result of code, break it AFTER the line executes. ON the line does nothing, as no execution happens there.
*records appear or the modifications appear in recordsets after they are updated only.

--> yup i know that, I tried a Refresh just before the rs.update on a second attempt to see if something happened and that's when i took the screen shot!

this:

objProps.Name = "Custom"

is referring to .NAME. it doesn't recognize the prop. hell, I wouldn't know why if that is really the case, because vba is one environment for all office programs. same syntax, same compiler, nothing changes so it's not like the lib is being read differently between xl and acc (I wouldn't think anyway!)

this:

Set objProps = objPropSets.Item("SummaryInformation")

may be running into confusion with DAO. DAO has elements named "property" and "properties". so I would assume acc is associating the code with DAO first. DAO is a default library in acc, whereas in xl it is not. I'm fairly sure that's the case with this one at least. or the other case may be that the default "database" objects referenced in the acc program are not there in xl, and so those are being looked at first by the compiler. DAO is used both in xl and acc, sorry! prefix those 2 decs with qualifier = lib name. e.g. -

dim objProps as LIBRARYNAME.property

do you agree?

---> That makes sense. I tried with the following code and it worked!

Option Compare Database
Public Sub UpdateTable()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    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 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)
            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

Thanks so much. Just another quick question, I want to update the table if the entry exists, according to the "File Name". (If the filename is in the DB, i do not want to add the entry, I want to modify the existing one). Does this imply that i have to set the primary key to the field "File Name" or something like that?

Thanks again so much, i wouldn't have figure the library thing out!

[Chronik]

*to see result of code, break it AFTER the line executes. ON the line does nothing, as no execution happens there.
*records appear or the modifications appear in recordsets after they are updated only.

--> yup i know that, I tried a Refresh just before the rs.update on a second attempt to see if something happened and that's when i took the screen shot!

this:

objProps.Name = "Custom"

is referring to .NAME. it doesn't recognize the prop. hell, I wouldn't know why if that is really the case, because vba is one environment for all office programs. same syntax, same compiler, nothing changes so it's not like the lib is being read differently between xl and acc (I wouldn't think anyway!)

this:

Set objProps = objPropSets.Item("SummaryInformation")

may be running into confusion with DAO. DAO has elements named "property" and "properties". so I would assume acc is associating the code with DAO first. DAO is a default library in acc, whereas in xl it is not. I'm fairly sure that's the case with this one at least. or the other case may be that the default "database" objects referenced in the acc program are not there in xl, and so those are being looked at first by the compiler. DAO is used both in xl and acc, sorry! prefix those 2 decs with qualifier = lib name. e.g. -

dim objProps as LIBRARYNAME.property

do you agree?

---> That makes sense. I tried with the following code and it worked!

Option Compare Database
Public Sub UpdateTable()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    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 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)
            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

Thanks so much. Just another quick question, I want to update the table if the entry exists, according to the "File Name". (If the filename is in the DB, i do not want to add the entry, I want to modify the existing one). Does this imply that i have to set the primary key to the field "File Name" or something like that? I deleted the ID and set the primary key to File Name, and now i'm trying to find out how to work this code out :

If Not IsNull(DLookup("[File Name]", "[Table1]", "[File Name]='" & colFiles(i) & "'")) Then
                rs.Edit
            Else
                rs.AddNew
                rs![File Name] = colFiles(i)
            End If

It does enter in rs.Edit when the entry exists, but it does not update the data. I changed the Title of the first "File Name" file in Solid Edge but the DB does not reflect the changes. I see that the line of code where rs!["Title"] = .. shows the new data when going thru a breakdown run.

Thanks again so much, i wouldn't have figure the library thing out!

[help_me_with_access]

Thanks so much. Just another quick question, I want to update the table if the entry exists, according to the "File Name". (If the filename is in the DB, i do not want to add the entry, I want to modify the existing one). Does this imply that i have to set the primary key to the field "File Name" or something like that? I deleted the ID and set the primary key to File Name, and now i'm trying to find out how to work this code out :

If Not IsNull(DLookup("[File Name]", "[Table1]", "[File Name]='" & colFiles(i) & "'")) Then
                rs.Edit
            Else
                rs.AddNew
                rs![File Name] = colFiles(i)
            End If

It does enter in rs.Edit when the entry exists, but it does not update the data. I changed the Title of the first "File Name" file in Solid Edge but the DB does not reflect the changes. I see that the line of code where rs!["Title"] = .. shows the new data when going thru a breakdown run.

Thanks again so much, i wouldn't have figure the library thing out!

the relevant actions are .EDIT, .UPDATE, and .ADDNEW. regardless of which initialization action you use, without .UPDATE as a successor, changes are never reflected. they are discarded, unless I'm mistaken.

put EDIT and ADDNEW in the conditional and UPDATE outside the conditional. that'll work fine. that way you update regardless of the preceeding actions.

by the way, don't use DLOOKUP. use DCOUNT() instead. reason - you don't have to deal with NULLs which are risky because MS doesn't have any consistency using them. it wastes time doing redundant research that MS should have fixed in v.1 of any of their programs.

also, if you're going to have any interaction with outside data sources at any time, in visual basic you should probably always be using the TRIM() function as enclosures on field values. I've run into multiple, and annoying instances, where so much data is imported you can't possibly track it with the human eye, but yet errors resulting in undetected whitespace take way too much time to fix.

but unfortunately, and again a Microsoft mistake, is that Visual Basic does NOT know the different between " " and "". and it's not even consistent. sometimes the 2 values may be val == val, and sometimes they might be val <> val. Your guess is as good as anyone's, but TRIM() seems to get rid of the potential pitfall.

hope this thread was a good experience for you. Was fun!

[Chronik]

Thanks for the tips!

The Edit is still not working though. I updated the title of file "C100.psm" in Solid Edge, but in the table it does not update (even with rs.update). I just realized that it updated the table for Filename "C150.psm" instead, which is not related at all.

The code goes in rs.edit but i'm pretty sure that it is not pointing toward any particular entry, so that makes him go random. Could that be it?

Thanks