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

[wedd]

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!:friends:

[Movian]

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.

[wedd]

Thanks, Movian! I did search on the web but I couldn't find anything substantial.

[HiTechCoach]

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.

[IanFScott]

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

[HiTechCoach]

This may also be helpful: The Connection String Reference (http://www.hitechcoach.com/index.php?option=com_weblinks&view=weblink&id=329:the-connection-string-reference&catid=87:microsoft-sql-server&Itemid=20)

[wedd]

Thanks IanFScott and HiTechCoach!