none
Visual basic / MS Access connection string

    Question

  • Hello

    I'm reading and writing data to an MS Access database via Excel Visual Basic code.

    I have previously used the following definition for the connection:

    .ConnectionString = "DSN=My Own Database;"

    But it seems to be better to define the whole connectionstring independently, instead of using a DSN.

    If i ask the code to tell me the current provider, when using the DSN, i get the following information:

    MsgBox "Connection provider: " & cnn.Provider

    This code tells me that the provider is "MSDASQL.1"

    All the examples i have studied, i get referred i should use "Microsoft.Ace.OLEDB.12.0" instead.

    What is the proper way to define the whole connection string?

     

    Tuesday, December 28, 2010 9:31 AM

Answers

All replies

  • Try

    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Databases\MyDatabase.accdb;Persist Security Info=False;"

    substituting the correct path and filename.


    Regards, Hans Vogelaar
    Tuesday, December 28, 2010 2:40 PM
  • Hi and thanks for your reply

    I tried using:

    .ConnectionString = “Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=Data Source=\\myserver\folder\mydatabase.accdb"

    Should i use that or:

    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\myserver\folder\mydatabase.accdb;Persist Security Info=False;"

    I dont know what the difference between those two are.

    Is the proper provider MSDASQL or Microsoft.ACE.OLEDB.12.0 ?

    What if the user has older version than 12.0 ?

    Connection is to an MS Access database.

     

    Wednesday, December 29, 2010 7:18 AM
  • Hi poppe2,

    Thank you for posting.

    >>I dont know what the difference between those two are.

    According to this article: http://www.connectionstrings.com/access , I think .ConnectionString = “Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=Data Source=\\myserver\folder\mydatabase.accdb" belongs to the type ODBC Driver while  .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\myserver\folder\mydatabase.accdb;Persist Security Info=False;" belongs to the type OLE DB Provider.

    In addition, here is the article about the difference between ODBC and OleDb: http://stackoverflow.com/questions/103167/what-is-the-difference-between-ole-db-and-odbc-data-sources

    >>Is the proper provider MSDASQL or Microsoft.ACE.OLEDB.12.0 ?

    1. What is MSDASQL?

    MSDASQL is an OLEDB/ODBC ‘bridge’ that allows applications built on OLEDB and ADO (which uses OLEDB internally) to access data sources through ODBC drivers. MSDASQL ships with the Windows Operating System, and Windows Server 2008 and Windows Vista SP1 are the first Windows releases to include a 64-bit version of MSDASQL.

     

    2. MSDASQL belongs to ODBC, such as you can define connection string: Provider=MSDASQL; Driver= {Microsoft Access Driver (*.mdb)}; DBQ=C:\path\filename.mdb; And this explain why cnn.Provider is "MSDASQL.1". For more information, you can reference this article: http://www.asp101.com/articles/john/connstring/default.asp

     

    3. ACE.OLEDB belongs to OLE DB. If you are an application developer using OLEDB, set the Provider argument of the ConnectionString property to “Microsoft.ACE.OLEDB.12.0”, this driver was released with Office 2007. It is possible to use the Microsoft.ACE.OLEDB.12.0 to connect to older .xls (Excel 97-2003) workbooks as well. 

    >>What if the user has older version than 12.0 ?

    You may got answer from this site: http://www.connectionstrings.com/access 

     

    I hope these can help you and feel free to follow up.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by Bruce Song Thursday, January 06, 2011 10:06 AM
    Tuesday, January 04, 2011 6:59 AM
  • Ok, thanks.

    But what is the difference between:

    Microsoft ACE OLEDB and Microsoft Jet OLEDB

    In some places ACE is adviced, others Jet. Which one to use?

     

     

    Wednesday, January 05, 2011 10:16 AM
  • Hi Poppe2,

    I think these articles may help you:

    http://www.access-programmers.co.uk/forums/showthread.php?t=176972

    http://www.vbforums.com/showthread.php?t=630281

    http://bytes.com/topic/access/answers/486483-difference-between-ace-jet

    As far as I know, ACE can also connect the file which is opening, while Jet can not.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by poppe2 Friday, January 07, 2011 8:12 AM
    Wednesday, January 05, 2011 11:02 AM
  • This is working perfectly, thanks for your help on this.
    Friday, November 16, 2012 4:56 PM
  • For ConnectionStrings, I always use this resource:

    http://www.connectionstrings.com/


    Ryan Shuell

    Saturday, November 17, 2012 4:21 AM