6 Replies Latest reply on Jan 18, 2012 12:03 PM by Ron McCarry

    adding refernces to task script

    Ron McCarry

      I would like a file convert task to also write to an MS Access table. Is there a way to add a refernce to the Access database engine withing the advanced scriptiong options form in the task add-on? IMO, it would be so much easer if EPDM included the VBA editor like in SolidWorks.

        • Re: adding refernces to task script
          Jeff Sweeney

          I haven't tested it, but I can't think of why you couldn't...I've controlled Word and Excel with an EPDM task.

           

          You won't be able to add a reference like what you can with a VBA editor, it will have to be an OBJECT.

           

          The only concern I would have for you is if you are running on a 64 bit system and using ADODB or JET drivers, I personally have lots of problems getting them to work. (Though I have heard this isn't an issue with Office 2010??)

           

          Please keep us in the loop for this project, I'm looking forward to learning how it goes!

            • Re: adding refernces to task script
              Ron McCarry

              So I would be using late binding and declare everyting as Object?

               

              I will give that a try and post back.

                • Re: adding refernces to task script
                  Jeff Sweeney

                  Yup. Here is a tip. Write and debug as much as you can in VBA. Then when you are sure you have it working, port the code to your task. Debugging inside of a task is very difficult.

                  • Re: adding refernces to task script
                    Ron McCarry

                     

                    The following code will open an access database inside the EPDM task add-on.

                     

                    There is a problem if any of your field names contains special characters and you need to use [ ]. The task add-on will replace any test in a string bracketed by [ ] with an empty string. I looks like it's treating it like a macro. So for example the following line.

                     

                    strSql = "SELECT tblParts.Description FROM tblParts WHERE (((tblParts.[PART#]) = '" & FileName & "'));"

                     

                    is changed in the VBA code to

                     

                    strSql = "SELECT tblParts.Description FROM tblParts WHERE (((tblParts.) = '" & FileName & "'));"

                     

                    Does anyone know how to escape these characters??? I even tried the following.

                    strSql = "SELECT tblParts.Description FROM tblParts WHERE (((tblParts." & "[" & "PART#" & "]" & ) = '" & FileName & "'));"

                     

                    Sub WritePAIPartPath(FileName As String, FullPath As String)

                    ' Debug.Assert False ' Un comment this line to debug

                    On Error GoTo errWritePAIPartPath

                     

                    ' Late binding to Microsoft ActiveX Data Objects 6.0 Library

                    ' Write the saved path for FileName, 1 for line written, 0 if none written

                    Const adOpenDynamic = 2

                    Const adLockReadOnly = 1

                    Dim oConnection As Object

                    Dim oRecordset As Object

                    Dim strSql As String

                    Dim lRecordsAffected As Long

                    ' Open connection to database file

                    Set oConnection = CreateObject("ADODB.Connection")

                    oConnection.Provider = "Microsoft.Jet.OLEDB.4.0"

                    oConnection.Open "File Path to Acess Database.mdb"

                    Set oRecordset = CreateObject("ADODB.Recordset")

                     

                    ' Check if filename is a part number

                    strSql = "SELECT tblParts.Description FROM tblParts WHERE (((tblParts.[PART#]) = '" & FileName & "'));"

                    oRecordset.Open strSql, oConnection, adOpenDynamic, adLockReadOnly

                    If oRecordset.EOF Then

                        Debug.Print oRecordset.Description

                    End If

                    oRecordset.Close

                    Set oRecordset = Nothing

                    oConnection.Close

                    Set oConnection = Nothing

                     

                    Exit Sub

                     

                    errWritePAIPartPath:

                    Log "Could not write path for " & FileName & vbCrLf & Err.Description

                    On Error Resume Next

                    oRecordset.Close

                    Set oRecordset = Nothing

                    oConnection.Close

                    Set oConnection = Nothing

                    End Sub