Excel and ODBC

[djabell]

I'm trying to populate a simple Excel worksheet from data within MS Access, setup as an ODBC datasource. I've looked up some sample code and every piece of code I've seen has a reference to the absolute path of the .mdb file. Is there a way to simply access the datasource the way ODBC *should* work, or is this yet another of Microsoft's added "features"? The code I've looked at involves the use of ADO to create a connection. I've got the code, I just don't want to have to reference the path of the Access file directly as this has security implications for the data I need to gain access to.

All help gratefully received!!!

 

[SkipVought]

hi,,
What do you mean by "access the datasource the way ODBC *should* work"???

You need a connect string. Now if you don't want to store that string in your workbook, then you can force a prompt to find the path, enter the id & password on the fly.

Is this what you want?

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[SkipVought]

BTW, I use this to access and capture the connect string

Sub GetADO_ConnectString()
      Dim conn As ADODB.Connection, dl As MSDASC.datalinks
      Dim strConn As String

      ' Assign the connection string to a variable.

      strConn = "DRIVER={SQL Server};SERVER=;UID=;PWD=;DATABASE="

      ' Create the Connection object.
      Set conn = New ADODB.Connection

      Set dl = New MSDASC.datalinks

      conn = dl.PromptNew
       'Assign the connection string and provider, then open the connection.
      With conn
        ActiveWorkbook.Sheets("sysParms").[Other] = .ConnectionString
'        .ConnectionString = strConn
'        .Provider = "MSDASQL"
'      ' Valid Enums for the ADO Prompt property are:
'      ' adPromptAlways = 1
'      ' adPromptComplete = 2
'      ' adPromptCompleteRequired = 3
'      ' adPromptNever =4
'        .Properties("Prompt") = adPromptAlways
        .Open
'        [ADO_ConnectString] = strConn
        .Close
      End With
      
      Set conn = Nothing
End Sub

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[djabell]

Cheers for your reply.

What I really mean is, can I connect to an ODBC datasource (with an Access file) from an Excel spreadsheet without having to specify the path of the .mdb file? When I've connected to ODBC in the past through Java, using the JDBC:ODBC bridge driver, I haven't had to specify the location of the .mdb file, all I've had to do is to state the driver (MS Access) and the odbc datasource and associated parameters (username, etc.) - that's what I mean by "...the way ODBC *should* work..". I have no problem storing the connection string, but I *do* mind having to specify the filename and location of the .mdb.

I'll give your code a go...cheers.

 

[fredericofonseca]

djabell,

You need to consider what is more beneficial to you

either have to setup a ODBC data source on every computer where your progrma is to run, or have a database location stored on either your Excel file or on a .ini or registry entry.

If you prefer to have the work of setting up a ODBC data source then the following link is all you need

http://www.carlprothman.net/Default.aspx?tabid=89#DSN

ON the other hand if you prefer the benefits of having more versatible connection then look at the following one.

http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForAccess

This last one does not require the ODBC data source to be created/maintained.

Your choice really. I prefer the last one.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[djabell]

Cheers - this is exactly what I wanted.