Results 1 to 5 of 5
  1. #1
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    SQL from VBA w/o subquery using ADO? (2000)

    Long ago, I wrote a Word VBA application to report data out of an Access database. The .mdb is the data store for a commercial time and billing program written in VB.

    Due to the table design (see illustration), it is necessary to do a very convoluted join to retrieve the actual client name and matter name for each time entry. To do this in one go, any time slip that does not have a valid client and a valid matter is omitted from the report. Since the report is an edit list, this is a problem.

    I know this problem can be solved rather easily by inserting an intermediate query into the database, and doing a right join to that query in my VBA query. AND there are no security restrictions on the database preventing me from doing this. BUT, to avoid being blamed for data loss and possibly voiding our support agreement, I have refrained from doing this, and simply added a warning to the report that certain data might be omitted.

    (Deep breath)

    So... I am just learning ADO, and I am wondering whether I have any new options for solving this problem. For example, it is conceivable that I could generate two snapshot recordsets and join them using ADO? A quick yea or nay, and any useful ideas, would be appreciated.

    (As a Plan B, if there is a way to determine which time slips do have not both a valid client and matter in a single query, i.e., the ones that are not joined in the below query, that would be almost as good.)
    Attached Images Attached Images

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL from VBA w/o subquery using ADO? (2000)

    Sorry, but SQL is SQL, whether you use ADO or DAO. There is more support for ANSI SQL in Access 2000, but that isn't really an ADO issue. When you get down to it, recordsets are declared as ADO or DAO, but they're still created using SQL.

    In fact, you should be able to get all your timeslips by creating your query using outer joins instead of inner joins. If you do an outer join from TimeSlip to Client and another outer join from TimeSlip to Matter and add the criteria "WHERE Matter.MatCliNbr = Client.CliSysNbr OR Client.CliSysNbr Is Null" instead of the join between Client and Matter, I think that should give you the records you want. Outer joins should give you all the TimeSlip records with any Matter and Client records that match plus any Matter records that match the TimeSlip but don't have a matching Client record. You may have to tweak the criteria a little, but it should be close.
    Charlotte

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: SQL from VBA w/o subquery using ADO? (2000)

    Unfortunately, I can't make that work. I've tried a number of combinations, and I either don't get the invalid record, or I just get an hourglass (testing interactively in Access).

    I'm leaning toward what I should have done long ago, which is adding my own .mdb to the solution. Now I only have to figure out (1) how to dynamically change the source of a linked table at runtime, and (2) how to package the install for a mixture of Win98/Win2k environments.

    (1) This works from a module inside Access:

    Sub test()
    ChangeLinkedDBPath "Client, "c:jts2"
    End Sub

    Sub ChangeLinkedDBPath(strTable As String, strFolderPath As String)
    'connection string format valid for Jet databases ONLY
    Dim tblClient As TableDef
    With CurrentDb
    .TableDefs(strTable).Connect = ";DATABASE=" & strFolderPath & "JTS2VAL.MDB"
    .TableDefs(strTable).RefreshLink
    End With
    ShowLinkedTblInfo
    End Sub

    Sub ShowLinkedTblInfo()
    Dim aTable As TableDef
    For Each aTable In CurrentDb.TableDefs
    If aTable.SourceTableName <> vbNullString Then
    Debug.Print aTable.Name & vbTab & aTable.Connect & vbTab & aTable.SourceTableName
    End If
    Next
    End Sub

    (1) This version works from inside a Word VBA module with a reference to Microsoft DAO 3.6:

    Sub TestPathChg()
    ChangeLinkedDBPath "Client", "C:JTS2"
    End Sub

    Sub ChangeLinkedDBPath(strTable As String, strFolderPath As String)
    'connection string format valid for Jet databases ONLY
    Dim dbTester As Database, tblClient As TableDef
    Set dbTester = OpenDatabase("T:tester.mdb")
    With dbTester
    .TableDefs(strTable).Connect = ";DATABASE=" & strFolderPath & "JTS2VAL.MDB"
    .TableDefs(strTable).RefreshLink
    End With
    ShowLinkedTblInfo dbTester
    dbTester.Close
    Set dbTester = Nothing
    End Sub

    Sub ShowLinkedTblInfo(dbObject As Database)
    Dim aTable As TableDef
    For Each aTable In dbObject.TableDefs
    If aTable.SourceTableName <> vbNullString Then
    Debug.Print aTable.Name & vbTab & aTable.Connect & vbTab & aTable.SourceTableName
    End If
    Next
    End Sub

    I think I'm on my way. (And I have no idea whether this low-level tinkering is even possible in ADO.)

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL from VBA w/o subquery using ADO? (2000)

    It's possible although you don't actually *have* to link the tables through ADO. Instead, you can create a connection in code to the database and then open the recordset on that connection. That means that nearly the same code should work in both Word and Access, but it will look quite different since ADO has an entirely different object model.

    If you do want to create a link using ADO, here's a routine to do that:
    <pre>Sub CreateLinkedAccessTable(strDBLinkFrom As String, _
    strDBLinkTo As String, _
    strLinkTbl As String, _
    strLinkTblAs As String)

    Dim catDB As ADOX.Catalog
    Dim tblLink As ADOX.Table

    Set catDB = New ADOX.Catalog
    <font color=448800>' Open a Catalog on the database in which to create the link. </font color=448800>
    catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & strDBLinkFrom

    Set tblLink = New ADOX.Table
    With tblLink
    <font color=448800>' Name the new Table and set its ParentCatalog property to the
    ' open Catalog to allow access to the Properties collection.</font color=448800>
    .Name = strLinkTblAs
    Set .ParentCatalog = catDB

    <font color=448800>' Set the properties to create the link.</font color=448800>
    .Properties("Jet OLEDB:Create Link") = True
    .Properties("Jet OLEDB:Link Datasource") = strDBLinkTo
    .Properties("Jet OLEDB:Remote Table Name") = strLinkTbl
    End With

    <font color=448800>' Append the table to the Tables collection.</font color=448800>
    catDB.Tables.Append tblLink

    Set catDB = Nothing
    End Sub <font color=448800>'CreateLinkedAccessTable(strDBLinkFro m As String, _
    strDBLinkTo As String, _
    strLinkTbl As String, _
    strLinkTblAs As String)</font color=448800></pre>

    Charlotte

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: SQL from VBA w/o subquery using ADO? (2000)

    Charlotte, thanks. I was shying away from ADOX thinking it probably was yet another thing I couldn't assume that users would have. Research shows that it first shipped with Office2000 (as part of ADO 2.1), so I might be able to rely on it being there. Worth taking a little survey around the office.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •