CODEWITHSUNDEEP
vbams-accessvisio
silicon mode
silicon mode

Reputation: 143

Connect Visio document to Access database

I want to add an Access database to my Visio document. The MSN example shows the string to connect to an Excel document. What would I use to connect to an Access DB?

I am Using:

Microsoft Visio 2013
Microsoft Access 2013
Microsoft Windows 7 Enterprise.

DB location:

C:\Users\j.Smith\Desktop\access_file.mdb

My script:

Public Sub AddDataRecordset_Example() 

    Dim strConnection As String 
    Dim strCommand As String 
    Dim strOfficePath As String 
    Dim vsoDataRecordset As Visio.DataRecordset 

    strOfficePath = Visio.Application.Path     
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _ 
                   & "User ID=Admin;" _ 
                   & "Data Source=" + strOfficePath + "SAMPLES\1033\ORGDATA.XLS;" _ 
                   & "Mode=Read;" _ 
                   & "Extended Properties=""HDR=YES;IMEX=1;MaxScanRows=0;Excel 12.0;"";" _ 
                   & "Jet OLEDB:Engine Type=34;" 

    strCommand = "SELECT * FROM [Sheet1$]" 

    Set vsoDataRecordset = ActiveDocument.DataRecordsets.Add(strConnection, strCommand, 0, "Org Data") 

End Sub

Upvotes: 1

Views: 1275

Answers (1)

David Parker
David Parker

Reputation: 519

This should do it:

strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "User ID=Admin;" & _
    "Data Source=" & accessFileName & ";" & _
    "Mode=Read;" & _
    "Extended Properties="""";" & _
    "Jet OLEDB:System database="""";" & _
    "Jet OLEDB:Engine Type=6;" & _
    "Jet OLEDB:Database Locking Mode=0;"
strCommand = "SELECT * FROM `tablename`"

Note the single quotes around table name.

Also, fyi, I have always found the following website very useful : ConnectionStrings

Upvotes: 1

Related Questions