Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    Chronik is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    17

    Guidelines on building a db from Solid Edge files

    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 :

    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!

  2. #2
    help_me_with_access is offline help_me_with_excel
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    262
    Quote Originally Posted by Chronik View Post
    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?

  3. #3
    Chronik is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    17
    Quote Originally Posted by help_me_with_access View Post
    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!

  4. #4
    help_me_with_access is offline help_me_with_excel
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    262
    well then that's simple. here's some pcode. the pcode is in red, everything else is verbatim:

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

  5. #5
    Chronik is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    17
    Quote Originally Posted by help_me_with_access View Post
    well then that's simple. here's some pcode. the pcode is in red, everything else is verbatim:

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

    Click image for larger version. 

Name:	Access - 001.PNG 
Views:	7 
Size:	22.9 KB 
ID:	8100

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

    Thanks

  6. #6
    help_me_with_access is offline help_me_with_excel
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    262
    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.

  7. #7
    Chronik is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    17
    Quote Originally Posted by help_me_with_access View Post
    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 :

    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!

  8. #8
    help_me_with_access is offline help_me_with_excel
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    262
    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?

  9. #9
    Chronik is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    17
    Quote Originally Posted by help_me_with_access View Post
    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 :
    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
    ?

    Thanks a lot for your help on this!

  10. #10
    Chronik is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    17
    Refs library in Access and Excel (Access left, excel right)
    Click image for larger version. 

Name:	Refs.PNG 
Views:	6 
Size:	105.3 KB 
ID:	8110

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

    Click image for larger version. 

Name:	rs.update.PNG 
Views:	5 
Size:	86.0 KB 
ID:	8111

    Thanks

  11. #11
    help_me_with_access is offline help_me_with_excel
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    262
    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:

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

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

    Code:
    dim objProps as LIBRARYNAME.property
    do you agree?

  12. #12
    Chronik is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    17
    Quote Originally Posted by help_me_with_access
    *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!

    Quote Originally Posted by help_me_with_access
    this:

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

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

    Code:
    dim objProps as LIBRARYNAME.property
    do you agree?
    ---> That makes sense. I tried with the following code and it worked!
    Code:
    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!

  13. #13
    Chronik is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    17
    Quote Originally Posted by help_me_with_access
    *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!

    Quote Originally Posted by help_me_with_access
    this:

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

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

    Code:
    dim objProps as LIBRARYNAME.property
    do you agree?
    ---> That makes sense. I tried with the following code and it worked!
    Code:
    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 :
    Code:
    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!

  14. #14
    help_me_with_access is offline help_me_with_excel
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    262
    Quote Originally Posted by Chronik View Post
    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 :
    Code:
    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!

  15. #15
    Chronik is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    17
    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

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. using mdb files which are backed up hourly into zip files
    By Robbie MacKinnon in forum Access
    Replies: 3
    Last Post: 05-05-2015, 05:02 AM
  2. Building Relationship: one to many
    By Keeper in forum Access
    Replies: 1
    Last Post: 04-25-2012, 05:31 PM
  3. Building a FAQ
    By Karin in forum Access
    Replies: 5
    Last Post: 03-07-2011, 11:26 AM
  4. Replies: 1
    Last Post: 02-21-2011, 09:55 PM
  5. Building
    By jlech1805 in forum Access
    Replies: 1
    Last Post: 11-17-2010, 12:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums