Is there a way to open an exe file from vba?

I am attempting to open the odbc management application and am stuck at the following:

Dim objFSO As Variant

    Set objFSO = CreateObject("Scripting.FileSystemObject")

    strFromPath = "C:\WINDOWS\system32\odbcad32.exe"
    If objFSO.FileExists(strFromPath) Then
        objFSO.OpenFile strFromPath
    End If

Is this a viable way or is there some other, better way.
Basically, when the user pushes the button I want to open the application so the user can set up new odbc conncetions in their Windows system.

Thanks for any help.
Coby

A better way to do this would be to make sure the DSN was installed that you needed for your application, or even better, to use provider statements instead of DSN calls. For example, to install a DSN, do this:

Dim sMyDSN As String

Set WshShell = WScript.CreateObject("WScript.Shell")

On Error Resume Next
Err.Clear
sMyDSN = WshShell.RegRead("HKLM\Software\ODBC\ODBC.INI\MyDS NName\Description")

If Err <> 0 Then 'install DSN WshShell.RegWrite "HKLM\Software\ODBC\ODBC.INI\MyDSNName\Description ", "SQL"
    WshShell.RegWrite "HKLM\Software\ODBC\ODBC.INI\MyDSNName\Driver" , "C:\Windows\System32\\SQLSRV32.dll"
    WshShell.RegWrite "HKLM\Software\ODBC\ODBC.INI\MyDSNName\LastUse r", "DefaultUserName"
    WshShell.RegWrite "HKLM\Software\ODBC\ODBC.INI\MyDSNName\Server" , "ServerName"
    WshShell.RegWrite "HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\MyDSNName", "SQL Server"
End If

This will install the DSN to the back end that is required. You can make this for an Access back end as well. Just install the DSN, and then go look at the registry entries needed.

A better solution is to use ADO and a provider statement rather than a DSN. Like this:

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim sSQL As String

Set cn = New ADODB.Connection
With cn
    .ConnectionString = CurrentProject.Connection
    .CursorLocation = adUseClient
End With
cn.Open "Provider=SQLOLEDB;Data Source=ServerName;Initial Catalog=DatabaseName;UID=UserID;PWD=Password"

sSQL = "SELECT * FROM MyTable"

Set rs = New ADODB.Recordset
rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic

etc.

If you are linking tables (not good for most applications) then you can just check the DSN and if it not there, install it without using an interface and relying on the user to do it themselves.

Did this help at all?


mmcdonal

Look it up at: http://wrox.books24x7.com

Yes! That worked for what I was trying to do.
I went with your first recommendation using the shell approach.
I had seen the shell being used a lot, never needed it before so knew nothing about it, now I am at that point and really like that method.

Thanks,
Coby.

mmcdonal,

I have another related question for you if you don't mind.

I want to use one Access project to house everything except for user data tables (ie, all of my forms, code modules, reports, and queries).

Then, I want to keep a main database external of the the project to house all the the data the users will build.

I am thinking about using a udl data link to accomplish this.

So, is there a way to establish the link to the tables database through the udl at open event of the database housing the code project? Then, query the tables as if they were part of the code database (as if they were the actual CurrentDb?)

OR, is there an easier way to approach this altogether?

Any ideas? Thanks so much for your help.

Coby Gifford.

Chapter 19 - Understanding Client-Server Development with VBA in Access 2007 VBA Programmer's Reference has a great section on Using Linked Tables in ACCDB/MDB Files. This covers another way of creating a DSN, and then linking tables as needed in code.

Here is the code to create the DSN:

 Public Sub CreateNewDSN()

         ' Define Variables
         Dim strDSNName As String
         Dim strDriverName As String
         Dim strDescription As String
         Dim strServer As String
         Dim strDatabase As String

         ' This is the DSN name to use when
         ' referencing the DSL in your code
         strDSNName = "TestDSN"

         ' The name of the ODBC Driver used for the connection
         strDriverName = "SQL Server"

         ' This is the optional description to use
         ' in the ODBC Driver Manager program
         strDescription = "Test DSN Description"

         ' In the case of SQL Server, use the following
         ' line of code to specify the SQL Server to connect to
         strServer = "<machine name>\<sqlexpress>"

         ' Then name of the Default database on the server
         ' used for this DSN. If not specified, then SQL Statements
         ' may end up getting executed against the master database
         strDatabase = "NorthwindCS"

         ' Create the DSN
         DBEngine.RegisterDatabase _
         strDSNName, strDriverName, _
         True, "Description=" & strDescription & _
         Chr(13) & "Server=" & strServer & _
         Chr(13) & "Database=" & strDatabase

     End Sub


Then to link tables:

Public Sub CreateLinkedTable()

              ' NOTE: This code requires the DAO object library to work.
              ' If you are using the SampleACCDB.accdb sample file, then this
              ' reference should already be present. When unsure, check the
              ' VBA project references under Tools -> References menu option

     ' in the VBA editor and make sure a reference is set to:
     ' 1. Access 2007: Microsoft Office 2007 Access database engine Object Library
     ' 2. Access 2003 and older: Microsoft DAO 3.6 Object library

     ' Define Variables
     Dim strConnection As String
     Dim daoTableDef As DAO.TableDef

     ' This must reference an existing DSN
     Const strDSNName = "TestDSN"

     ' The application name can be used for tracing and
     ' troubleshooting the source of problems on the server.
     ' This can be anything, but usually the more specific the better.
     Const strAppName = "Microsoft Office Access 2007"

     ' The database where the table resides on SQL Server
     Const strDatabase = "NorthwindCS"

     ' User name for logging into the database server. This could be
     ' captured by a logon form and stored in a global variable.
     Const strUserName = "sa"

     ' Password for logging in to the database server. This could
     ' be captured by a logon form and stored in a global variable.
     Const strPassword = "password"

     ' Then name of the table on the remote server
     Const strRemoteTableName = "Customers"

     ' The name of the table we want create in the local file
     ' that links to the Remote Table
     Const strLocalTableName = "dbo_Customers"

     ' This will build the ODBC connection string for our new table
     strConnection = _
         "ODBC:" & _
         "DSN=" & strDSNName & ";" & _
         "APP=" & strAppName & ";" & _
         "DATABASE=" & strDatabase & ";" & _
         "UID=" & strUserName & ";" & _
         "PWD=" & strPassword & ";" & _
         "TABLE=" & strRemoteTableName

     ' This creates a new table object and adds it to the local
     ' database. If your tables already exist, then you would
     ' skip this code and use code to refresh the links, instead
     Set daoTableDef = CurrentDb.CreateTableDef( _
                    strLocalTableName, _
                    dbAttachSavePWD, _
                    strRemoteTableName, _
                    strConnection)

         CurrentDb.TableDefs.Append daoTableDef

         ' Clean up
         Set daoTableDef = Nothing

     End Sub

Or to refresh links:

Sub RefreshTable()

         ' Define Variables
         Dim daoTableDef As DAO.TableDef

         ' The name of the local linked table to refresh
         strLocalTableName = "dbo_Customers"

         ' This will build the ODBC connection string for our new table
         strConnection = _
         "ODBC:DSN=TestDSN;APP=Microsoft Office Access 2007;" & _
         "DATABASE=NorthwindCS;UID=sa;PWD=password;TABLE=Cu stomers"

         ' This code assumes that the linked table object have
         ' already been created and only need to be refershed.
         Set daoTableDef = CurrentDb.TableDefs(strLocalTableName)
         daoTableDef.Connect = strConnection
         daoTableDef.RefreshLink

         ' Clean up
         Set daoTableDef = Nothing

     End Sub


I am not familiar with UDL files.

mmcdonal

Look it up at: http://wrox.books24x7.com