Consulting

Results 1 to 7 of 7

Thread: Is it possible to use vba programming to communicate with other database systems

  1. #1
    VBAX Mentor
    Joined
    Aug 2010
    Posts
    457
    Location

    Is it possible to use vba programming to communicate with other database systems

    Hi experts!


    I was wondering on the scope of vba programming. Is it possible to use vba programming to communicate with other database systems; in particular non Microsoft systems. If so, would you know of any sample code to do this or websites that illustrate how vba programming can import data from Access into non Microsoft Database systems? Thanks for the knowledge! And thanks for your contributions!

  2. #2
    VBAX Mentor Movian's Avatar
    Joined
    Aug 2008
    Location
    NC, USA
    Posts
    399
    You can connect to any DB system that supports DAO or ADO.

    For example My system connects to a MySQL database on the web for licensing (And to a different MySQL db through PHP scripts for some other functions). But connects to a Microsoft SQL for its main tables. Both use ADO connections.
    Note: you will need to include the Microsoft ActiveX Data objects 2.6 or later in your references for ADO to work (i know thats not technicaly acurate but it conveys enough info for now)
    there are many examples on the web, have a look around. If your still stuck i will see what i can find.
    "From the ashes of disaster grow the roses of success" - Chitty chitty bang bang

    "I fear not the man who has 10,000 kicks practiced once. I fear the man who has 1 kick practiced 10,000 times" - Bruce Lee

  3. #3
    VBAX Mentor
    Joined
    Aug 2010
    Posts
    457
    Location
    Thanks, Movian! I did search on the web but I couldn't find anything substantial.

  4. #4
    Quote Originally Posted by wedd
    Thanks, Movian! I did search on the web but I couldn't find anything substantial.
    Did you search on ADO and ODBC?

    Access's VBA can use ADO to work with any ODBC complaint database. If the database wyou want o work with has an ODBC driver for your OS then you are should good to go.
    Boyd Trimmell aka HiTechCoach
    Microsoft Access MVP -2010-2015

    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Search for 'ADO Connection strings'. This will give you example strings to connect to almost any database.
    Set a reference to Microsoft ActiveX Data objects 2.6 or later in the VBE (Tools | References).
    In a module type:
    Dim conTest as New ADODB.Connection

    Then, using the example connection strings as a basis and VBA help on the Connection object to gain access to the database. Something like:

    conTest.Open "Provider=MSDASQL;" & _
    "Driver={SQL Server};" & _
    "Server=COMPCDN018;" & _
    "Database=parts", Username="purchasing", Password="letmein"


    You will know you have access when 'conTest.Open' functions without an error.

    You may need some trial and error to get it right but once connected you can get at any of the data in the database with an ADODB.Recordset or using SQL commands with and ADOD.Command.
    e.g. (after having successfully opened a connection using conTest)
    Dim RSTest as New ADODB.Recordset
    RSTest.Open "SELECT * From Widgets;", conTest, adOpenDynamic, adLockReadOnly

    Note: Intelisence will help with completing the parameters

  6. #6
    This may also be helpful: The Connection String Reference
    Boyd Trimmell aka HiTechCoach
    Microsoft Access MVP -2010-2015

    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  7. #7
    VBAX Mentor
    Joined
    Aug 2010
    Posts
    457
    Location
    Thanks IanFScott and HiTechCoach!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •