using DataRecordsets.Add, NOT visCmdAddDataRecordset - SOLVED

[jik_ff]

I hope I got the right command for this.  As I stated in another post, this should be the last peice of the puzzle for my floor plan database/visio combo.

So at this point I have a template file that will create new template pages (with adding a specificed map image to the background), add a default Cubical shape object, and add the Lengend color features that I have worked on in other posts.  The only thing missing is the initial linkage to the data.

In the near future, I am hoping to have all our offices tied to the same SQL database (instead of an Access file for each), but that may take some time, and I need something in production now.

My idea is this:

When the file is first open, it has 2 pages.  The first page, which will be the first map floor, and the second page which has a button that will duplicate itself and request the map Jpeg file for the background (and set all layers, locks, etc.)
These are done.  On the first page is some ducumentation to help get started and a button to click.  when this button is clicked, it should prompt for a map file (as with the template page, no problem), as well as request the source for the linked data.

I would like to automate this process as much as possible.  The data source field names and the shape data field names are the same, so, in theory(?) it should be easy to link it together.  I have looked up the visCmdAddDataRecordset (I think this is the right command, but I could be wrong) but can find no info on it besides it's int value.

[Thomas Winkel]

I can give you some code that I extracetd from my project to start with.
First create:

• Button: CommandButtonConnectToList
• Combo Box: ComboBoxListSelection
• An Excel File with three Tabs: "Tab1", "Tab2", "Tab2" and some data

Code Select Expand


Private Sub CommandButtonConnectToList_Click()
  Dim strConnection As String
  Dim strCommand As String
  Dim vsoDataRecordsetTab1 As Visio.DataRecordset
  Dim vsoDataRecordsetTab2 As Visio.DataRecordset
  Dim vsoDataRecordsetTab3 As Visio.DataRecordset
  strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _
    & "User ID=Admin;" _
    & "Data Source=" + ComboBoxListSelection.Text + ";" _
    & "Mode=Read;" _
    & "Extended Properties=""HDR=YES;IMEX=1;MaxScanRows=0;Excel 12.0;"";" _
    & "Jet OLEDB:Engine Type=34;"
  strCommand = "SELECT * FROM [Tab1$]"
  Set vsoDataRecordsetTab1 = ActiveDocument.DataRecordsets.Add(strConnection, strCommand, 0, "Tab1")
  strCommand = "SELECT * FROM [Tab2$]"
  Set vsoDataRecordsetTab2 = ActiveDocument.DataRecordsets.Add(strConnection, strCommand, 0, "Tab2")
  strCommand = "SELECT * FROM [Tab3$]"
  Set vsoDataRecordsetTab3 = ActiveDocument.DataRecordsets.Add(strConnection, strCommand, 0, "Tab3")
  Application.ActiveWindow.Windows.ItemFromID(visWinIDExternalData).Visible = True
End Sub


[jik_ff]

Learning all the time...

Well, I found out what I needed and a little bit about recording macros while searching for how to do this.  the full code (I got with Visio 2007 and Access 2007) was:

Code Select Expand

Sub TestAccessLinkage()

    Dim AccessStr as String

    accessSrt = "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" _
    & "Data Source=C:\YourPath\YourAceessDB.accdb;Mode=Read;Extended Properties="""";" _
    & "Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=6;" _
    & "Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;" _
    & "Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";" _
    & "Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;" _
    & "Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;" _
    & "Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False"


    Application.ActiveDocument.DataRecordsets.Add accessStr, "select * from `Table/Query Name`", 0, "Data Link Name"

    Dim vsoPrimaryKeys1(1 To 1) As String
    vsoPrimaryKeys1(1) = "KEY field name"
    Application.ActiveDocument.DataRecordsets.ItemFromID(28).SetPrimaryKey VisPrimaryKeySettings.visKeySingle, vsoPrimaryKeys1

    Application.ActiveWindow.Windows.ItemFromID(visWinIDExternalData).Visible = True

End Sub

Where :
C:\YourPath\YourAceessDB.accdb would be the path and access file name
"select * from `Table/Query Name`" would be the table or query to pull the data
Data Link Name would be the name that the data link would be called in Visio
KEY field name would be the Primary Key field in your data

When I found something similar some of the Jet commands were ommitted (most of the ones that = ""), but I left all the macro created.  For me I will next substitute the file and path (which will be somewhat hardcoded) and the Table name.  In anycase, what I was looking for was :

Application.ActiveDocument.DataRecordsets.Add

[jik_ff]

I can give you some code that I extracetd from my project to start with.
First create:
•Button: CommandButtonConnectToList
•Combo Box: ComboBoxListSelection
•An Excel File with three Tabs: "Tab1", "Tab2", "Tab2" and some data


...

Thanks ThomasWi, though I just found it.  I'm surprised they have not created constants for all that JET stuff, though I don't have a clue what it all means...

[jik_ff]

@ThomasWi

Question for you...  I was hoping to tie this Visio file to a query.  Is it possible to pass the Query a criteria for one of the fields?  In access the criteria is [Office Location] which prompts for an office location to be entered before the query will run.  Is it possible to do that in the VBA code here?  I am guessing it would be a (Select * from "'QueryName'" Where LocCode = <UserInput>) or something to that nature?  I'm going to fumble around with it a bit and check back.

[aledlund]

There's generally two ways to make a parameterized call. The first way is to create your query (in both Access and SQL you can use query builder and then view it as a SQL call) and then use the  query string as input to your method in VBA.

    Dim strQuery As String
    strQuery = "SELECT tblAppSolution.*" _
                & " FROM tblAppSolution" _
                & ";"

or

    ' get the last primary key entered into the table (primaryfield and mytable)   
    Dim strQuery As String
    strQuery = "SELECT MAX([" & primaryField & "]) FROM [" & myTable & "];"


For queries that are more complex and can require heavy processing I try to keep them in the database system (once again Access or SQL) and use a parameterized ADO call.

al

Code Select Expand



'
' use a stored procedure
' pass in a record key and return the record
'
Public Sub dbSelectAppSolutionByKey(ByVal intAppSolKey As Integer)

    On Error GoTo ErrHandler

    initProperties
    appsolKey = intAppSolKey

    CloseSession

    Dim rstReturn As ADODB.Recordset
    Set rstReturn = New ADODB.Recordset
       
    pAdoDbConn.ConnectionString = pAdoDbConnStr
    pAdoDbConn.Open
    pAdoDbCmd.ActiveConnection = pAdoDbConn
    pAdoDbCmd.CommandText = "selectAppSolByKey"
    pAdoDbCmd.CommandType = adCmdStoredProc
    pAdoDbCmd.NamedParameters = True
    pAdoDbCmd.Parameters.Refresh
   
    Dim prmTemp As ADODB.Parameter
    Set prmTemp = pAdoDbCmd.CreateParameter("@appsolKey", adInteger, adParamInput)
    prmTemp.value = intAppSolKey
    pAdoDbCmd.Parameters.Append prmTemp
   
    Set rstReturn = pAdoDbCmd.Execute
   
    ' test to be sure something came back
    If (rstReturn.BOF And rstReturn.EOF) Then
        CloseSession
        GoTo ExitHandler
    Else
        Set pAdoDbRecords = rstReturn
    End If

    pAdoDbRecords.MoveFirst
    Do While Not pAdoDbRecords.EOF
        pAdoDbRecCt = pAdoDbRecCt + 1
        pAdoDbRecords.MoveNext
    Loop

MoveRecord:
    MoveRecordToProperties

ExitHandler:
    Exit Sub
ErrHandler:
    If Err.Number <> 3021 Then  ' 3021 is the eof message
        Debug.Print "dbSelectAppSolByKey "; Err.Number & " " & Err.Description
    Else
        GoTo MoveRecord
    End If
End Sub




[jik_ff]

Thanks aledlund,

I will try that.  I'm not sure If I want to be storing the query in the database, maybe you might be the judge of if I should or not.  The idea is the SQL database to house the office information for multiple offices.  During the call to the data, you would be selecting only the office code associated with the Visio Floor Pan.  For instance, for the Toronto office, you would select only the records that have TOR in the LocCode field.

I don't really want to be making a new stored query for each office if I can get away with it.  The idea is that when they start either the Access front end or the Visio file, the office code would be selected so that they would only see and be able to manipulate those records.  In the Visio file, this is set the first time the file template is used (and stored on the page to be referenced).

If this even seems a bit too intensive then I will bite the bullet.  I just didn't want to have to make a new query for each office, or have to have different versions of the Access front end to manage that.  That would be more management on my side.

[aledlund]

Building a string dynamically (the second example) or using a stored procedure call for me has been really based upon how many records I thought were going to come back. The other advantage for an SP is that you can test it at the database to verify that the correct data is being sent back to the query.
The performance of a network based application that has a need for data access is heavily dependent upon how many records are retrieved. The beauty of parameterized calls (whether string or SP) is that you don't have to build unique ones for all possible combinations, it plays to how the data tables are designed. In either case you don't make unique call for each office, but rather a generic call that passes a parameter for the office that you want the results for.
al

[jik_ff]

To be honest aledlund,

Most of the code you supplied is going over my head.  Right now I can grab the data from the query (with the code I have):

Code Select Expand

   'accessStr is correct and this runs
    sqlSelect = "SELECT * FROM `0FloorPlan Query`
   
    Application.ActiveDocument.DataRecordsets.Add accessStr, sqlSelect, 0, "TestGo"

So this code runs, and brings in the data.  Path and filename for the access database will be hard coded (as the files work together and need to be in the same folder location).  The only thing I need to do is reduce the number of records based on the location.  There is a field called LocCode which is the field I want to test the WHERE against.  Following code I have seen around, it would appear that this code would be valid:

sqlSelect = "SELECT * FROM `0FloorPlan Query` WHERE [LocCode] like `TOR`"

But alas, it does not.  The `TOR` would later be changed to a var, but I need the code to work first, then I'll play with inputs.

[aledlund]

maybe we should take this off-line
edlund60014
lives over at
yahoo
dot com.

al

[jik_ff]

OK, finally got this to work.  It's all about syntax.  Problem is that it's not always apparent where to find the answers.  Thanks all that assisted me, even if you did not specifically solve this issue.  The more avenues you opened for me to explore, the more I learned.

It took looking at this statement for me to find my answer (quote from  http://fontstuff.com/access/acctut15.htm#writeSQL):

In case you haven't already noticed, I always write the SQL keywords in capitals (upper case). Access doesn't care if you do this or not but you will find your code much easier to read and understand if you do. Compare these two statements:

  Select tblStaff.Firstname, tblStaff.Lastname from tblStaff where tblStaff.Office="Paris";

  SELECT tblStaff.Firstname, tblStaff.Lastname FROM tblStaff WHERE tblStaff.Office="Paris";

...

There is more to it, but this is what got me over the hump for the WHERE clause.  I had this:

Code Select Expand

sqlSelect = "SELECT * FROM [0FloorPlan Query] WHERE [0FloorPlan Query].LocCode = 'TOR'"

And what it needed to be was this:

Code Select Expand

sqlSelect = "SELECT * FROM [0FloorPlan Query] WHERE [0FloorPlan Query].LocCode = 'TOR';"

Yes, I did go and add the [] as well as forcing the table/query name for the WHERE field check, though they did not need to be there.  For those that did not catch this, it seems that you need a ; at the end of the statement.  For some reason this is not needed when I remove the WHERE part of the statement (which really threw me off).

In anycase, problem solved, and I have even added in my var that is set when my Floor Plan drawing is initialized:

Code Select Expand

sqlSelect = "SELECT * FROM [0FloorPlan Query] WHERE [0FloorPlan Query].LocCode = '" & offCode & "';"