Access Data Project

[Jens Mertens]

Hi NG,

is it possible to create an access data project to connect to a
mysql-database to be able to create new mysql-tables? if yes, how can I do
this?

or is it only possible to connect to a mssql-server?

cheers, jens

 

[Guest]

Hey Jens,

Honestly, I have no clue. But if I were going to try whatever it is you're
trying to do, you can try creating table links. Go to File > Get External
Data > Link Tables, and you will get an open file window. If you scroll down
the file types combo box, you might find what you're looking for listed in
there. Otherwise, at the very bottom, you'll find "ODBC Databases". This is
probably what you're going to want to use. Look under the second tab
(Machine Data Source) and click the New button. SQL Server is the bottom
option.

.... you're going to have to take it from there. I really don't have a clue
what to do from there, because I only have Access installed on my machine.
Something I wrote in VB6 though might help out - it probably isn't exactly
like this in VBA, but it may be close:


'Sub Main()
' On Error Resume Next
' Dim objConn As New ADODB.Connection
' Dim objRs As New ADODB.Recordset
' On Error GoTo EH
' ConnectionString = "Provider=Microsoft Jet 4.0 OLE DB Provider;Data
Source=C:\Program Files\MVC Marketing\bin\prototype.mdb;Jet OLEDB:System
database=M:\MVC Marketing\_prototype\bin\workgroup.mdw;User
ID=mvcinfo;Password=mvcinfo;"
' objConn.Open ConnectionString
' CommandText = "SELECT [version] FROM [_local] WHERE [index]=1"
' Set objRs = objConn.Execute(CommandText)
' verlocal = objRs(0)
' CommandText = "SELECT [version] FROM [_settings] WHERE [index]=1"
' Set objRs = objConn.Execute(CommandText)
' vernetwork = objRs(0)
' objRs.Close
' objConn.Close
' Set objRs = Nothing
' Set objConn = Nothing
' if verlocal < vernetwork then

VB6 isn't as userfriendly as VBA when it comes to databases, and you need to
use connection strings and such to connect to databases. If you can't get
the tables to link, you could try using something like this code. Though a
warning ahead of time - it's just as bad as it looks.

hth,

Nick

 

[Sylvain Lafontaine]

To my knowledge, it's only possible to connect with a MS SQL-Server because
the OLEDB driver used by ADP for connecting to SQL-Server is not a standard
OLEDB provider. I don't think that it will work with MySQL. However,
nothing forbid you to give it a try but I will be very surprised if it
works.

My current opinion is that's impossible and won't works but I never tried it
personally.

 

[Larry Linson]

ADPs only work with Microsoft SQL Server.

If you can create tables in MySQL with Queries, and have the necessary
permissions, you can use MyODBC, and run those as passthrough Queries. As I
have never used Access as a client to MySQL, I can't say if MyODBC has some
restrictions that would prevent what you want to do -- but I would not
expect that it does anything to Passthrough Queries, as that is their
purpose.

Larry Linson
Microsoft Access MVP

 

[Albert D.Kallal]

Hi NG,

is it possible to create an access data project to connect to a
mysql-database to be able to create new mysql-tables? if yes, how can I do
this?

ADP (access projects) only work with sql server. The reason for most of this
is due to the fact that DDL commands (and features) for most sql sever
products (sql server, oracle, etc) are all different. The other problem is
that features like referential integrity also are different, or not
available with some database servers.

or is it only possible to connect to a mssql-server?

For ADP projects, you can only use sql server (the big advantage of ADP
projects is that the Relational window, and the table design of MS-ACCESS
can be used. If there was a 100% clone of sql server, then could also use
that.

Note that you can use the free edition of sql server, and the last 3
versions of ms-access/office have included this free edition of sql server
on the office cd for use with ms-access.

However, you can use any database sever product that offers ODBC. So, that
means you can use linked tables to oracle, or in your case MySql, and
ms-access will work just fine. So, the only restrictions on what server
product you use is when you create a ADP project. Normal access
mdb files and linked talbes work fine with any database server
(there is not restrictions).

So, you can well use ms-access as a ODBC client to Oracle, Sql sever, or in
your case MySql. So, there is really no restrictions on what database server
product you use. Obviously, ms-access will not 'know' how to table design
for those other products..and they all tend to be a bit different anyway.

So, only ADP projects are restricted to sql server since they are based on
sql specific features.

Note that you can use the free edition of sql server both ways with
ms-access (linked tables...or a ADP project). And, if you use the
free sql server express edition of sql server, then ADP projects
can work, but restriction messages will crop up. You are actually better off
to
used linked odbc tables with sql server express right now.