GUI Interface for MySQL 2

[asimasm]

Hi
I m new to my SQL. Ihave been using SQL Server and MS Access in the past. Can any one tell me if My SQL has a graphical interface like MS SQL server and MS Access. Whcih would allow me to visually manipulate database objects and also desighn queries/views visually.

Thanks
Asim

 

[sleipnir214]

On what platform do you need to run this?

If Win32, take a look at MySQL-Front. It is, however, more like Microsoft's SQL Enterprise Manager than Access.






Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[LittleSmudge]

I use MSAccess 2000

I use PassThrough queries for quick access to read only lookups

and ADO recordsets to open on specific records,
populate unbound forms and then write data back after changing


ALL the flexibility and power of MSAccess - but taking advantage of the client server structure via ODBC



'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[LittleSmudge]

Ooops - sorry Asim - I'm talking at cross purposes.

I was talking about a USER GUI not a db admin GUI


For the admin GUI I use the MySQL Control Centre 0.8.10 that is availalbe from the MySQL download site.


However, having taken a quick look at sleipnir214's link - I think I'll be investigation that a bit more in the future.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[asfaw]

Hello LittleSmudge,

I have used Access for various databases. I am now trying to use Access as FE with a back-end MySQL database. Since I have not used ADO before would you be able to share a sample database I can use to learn.

Your help is very much appreciated.

Maru

 

[LittleSmudge]

Basic approach to running Access as a front end to mySQL is as follows:-

To make the most of the Client-Server structure you DO NOT link tables from MySQL to the access front end via ODBC.

If you want to READ data then use Pass Through Queries
Define query in the usual way then from the Query Menu select SQL Specific and define it as PassThrough. Then set theOBDC link to your MySQL database.

For Forms:-
Comtinuous forms are bound at runtime to dynamically created ADO.Recordsets
All other forms are UNbound and are populated from Recordsets.

Every form has it's own mblnIsDirty boolean variable
Set to False in the On_Load event
Set to True in the After_Update event of every data control on the form
when closing the form If mblnIsdoirty then open recordset and write data back to table.

Reports are all bound to PassThrough queries.


The following is a snippet from my StandardCodes.Doc

Dim mblnIsDirty As Boolean

Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
If mblnIsDirty Then
    Select Case MsgBox("Do you want to save the changes you have made to this record ?" & vbLf _
                     & "Click Yes to Save and Close" & vbLf _
                     & "Click No to close without saving" & vbLf _
                     & "Click Cancel to return to the form." _
                     , vbYesNoCancel _
                     , "Data on the form has changed")
    Case Is = vbYes
        Call SaveRecord(Nz(TestId, 0))
    Case Is = vbNo
        ' don't save
    Case Else
        Exit Sub
    End Select
End If
DoCmd.Close

Exit_cmdClose_Click:
    Exit Sub
Err_cmdClose_Click:
    MsgBox Err.Description, , Err.Number
    Resume Exit_cmdClose_Click
End Sub

Private Sub Form_Activate()
DoCmd.Maximize
End Sub

Private Sub Form_Load()
On Error GoTo Err_Form_Load
Dim rstMy As ADODB.Recordset
mblnIsDirty = False
cmdSaveChanges.Enabled = False
If IsNull(OpenArgs) Then
    ' Add new record mode
Else
    Set rstMy = New ADODB.Recordset
    rstMy.Open "SELECT * FROM tblTest WHERE TestId = " & OpenArgs, MyConn
    If rstMy.EOF Then
    Else
        Call SetAllControls(rstMy)
    End If
    rstMy.Close
    Set rstMy = Nothing
End If
Exit_Form_Load:
Exit Sub
Err_Form_Load:
If Err.Number = 3709 Then   ' MyConn = nothing
    Call SetMyConn
    Resume
Else
    MsgBox Err.Description, , Err.Number
    Resume Exit_Form_Load
End If
End Sub

‘ Control Procedures
‘ # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
‘ General Procedures

Private Function NowDirty()
mblnIsDirty = True
cmdSaveChanges.Enabled = True
End Function

Private Sub SaveRecord(RecId As Long)
On Error GoTo Err_SaveRecord
Dim rstMy As ADODB.Recordset
Set rstMy = New ADODB.Recordset
rstMy.CursorType = adOpenKeyset
rstMy.LockType = adLockOptimistic
rstMy.Open "SELECT * FROM tblTest WHERE TestId = " & RecId, MyConn
If rstMy.EOF Then
    rstMy.AddNew
End If

rstMy!TestText = TestText
' ADD ALL FIELDS HERE
rstMy.Update
' If this is saving a new record then update the screen display of the TPReqId
If IsNull(TestId) Then
    rstMy.Close
    rstMy.Open "SELECT Last_Insert_Id() As TestId"
    TestId = rstMy!TestId
    RecId = TestId  ' Returns the new Id to the calling proc. _
                        ( To verify okay completion )
End If
rstMy.Close
Set rstMy = Nothing

Exit_SaveRecord:
Exit Sub
Err_SaveRecord:
If Err.Number = 3709 Then   ' MyConn = nothing
    Call SetMyConn
    Resume
Else
    MsgBox Err.Description, , Err.Number
    Resume Exit_SaveRecord
End If
End Sub

Private Sub SetAllControls(rst As ADODB.Recordset)
TestId = rst!TestId
TestText = rst!TestText
' ADD ALL FIELDS HERE

End Sub

Dig the bones out of that lot.


'ope-it-'elps.

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[masalachol]

To me the best one in the market is navicat

 

[asfaw]

Hello LittleSmudge,

I must say thank you very much for your kind and helpful response. I have read recently what you said in you response “DO NOT link tables from RDBMS like MySQL to make the most of a Client-Server system”. It will not perform well and it is not portable among many reasons they gave. It all makes sense to me.

I am used to linking tables and ADO is new to me. Would you mind sharing a working Access 97 or 2000 / MySQL sample file that I can use to learn. I will use what you have kindly provided to see how the code works. If there is any literature you have come across would also help.

Many thanks.

Maru

 

[pwesson]

Give this a try...

http://microolap.com/dba/mysql/designer/

Paul Wesson, Programmer/Analyst

http://www.ipaw.net

 

[asfaw]

Thank you very much for you kind help. The tool offers a lot of functionality. My specific problem is as follows:

I would like to use Micorosft Access as a front-end tool to a MySQL back-end RDBMS. Is there a sample database I can use to learn this. Your help is very much appreciated. I would like to use Micorosft Access because I am confortable and that is what the users use.

Maru

 

[LittleSmudge]

A97 only supports DAO ( as standard - although you can fudge it )

So if you want to use ADO then use A2k or later


All the code you need is in the above post

Create an unbound form with unbound controls.
One text box control for each field in your underlying table
( and for simplicity sake set the name of the control to be the same as the name of the field. )

Then just apply the code above to that form and it will work.

Look up in Help any Method or Property that you've not come across before.

I don't think here's anything more you need other than what's in the sample above.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[asfaw]

Hell LittleSmudge,

Thank you very much. I will for sure try the sample you provided. Would there be a book you would suggest?

I am not sure whether the functionality I am used to in a bound forms can be available in unbound forms including sub-forms. I am assuming reports would also use a similar code and/or pass through query (which I have no idea who to create one).

I do not want to take more of you time.

-Maru

 

[LittleSmudge]

PassThrough queries are easy peasy

Create a query in the usual way ( QBE grid design )
Then from the query menu select SQL specific , PassThrough.
Then open the properties box and in the ODBC property click on the three dots to run the wizard to connect to your ODBC source.


I've not found any functionality that I could do in a bound form that can't be done in unbound forms.
( Now there's a challenge ! )

It's just that sometimes you have to do it a little differently.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[asfaw]

Thank you very much. Yes you are right it is a challenge and an opportunity to learn doing it differently.

Maru

 

[Edski]

Quote:
"If you want to READ data then use Pass Through Queries"

LittleSmudge,
What if I wanted to WRITE data to MySQL from Access Tables and Databases?

Thanks (in advance)

 

[LittleSmudge]

That depends on where you're coming from.

If you have the data in controls on a form then the
Private Sub SaveRecord(RecId As Long)
procedure in the big block of code above does it for you.


If you have data in an access table that you just want to copy into MySQL as part of the conversion process then the approach I've used is either:-

If the data needs fiddling with ( data convertions etc )
Open ADO Recordset on Source
Open Another ADO Recordset on the MySQL target via ODBC ( similar to "SaveRecord" above
Then cycle through the records and do the convertion one record at a time

Otherwise
Create a Link in the Access database to the MySQL tables ( File Menu, Get External Data, Link Tables .. . )

Then Use an append Query to take records from the local table to the mySQL Linked table.

OR Use the same update query - but define that as PassThrough - as describle above ( Because it's only Pass Through SELECT queries that are Read Only )




'ope-that-elps.

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Edski]

Thanks lil'Smudge,

My project would be covered by what you wrote after the 'Otherwise' word.

I've done that (created the linked tables via ODBC), and then created an INSERT INTO tbl_name IN database_name SELECT * FROM tbl query and then used the dbs.Execute command to transfer the data across to MySQL.

My question now is: Is this the most efficient way to do this or should I use a pass-through query. If I use a pass-through query does that mean I don't link the tables? Can you show me a simple example of a pass-through query?

By efficient, I mean speed. I note your comment above about NOT using linked tables "to make the most of the Client-Server structure". Could you elaborate on that?

Again thanks (in advnace)

 

[Edski]

Addendum...

Actually, I know how to create a pass-through query, but want I want to do is call this query from within VBA using variables. I have thousands of (identically structured) little tables stored in Access and I am merging them ALL into a MySQL table. I am using a For Each ... Next loop to do this in VBA so I'm not sure if I can do it using a pass-through query. :-(

or can I....

 

[LittleSmudge]

First off you don't need the IN Database bit in your INSERT INTO string.



If you look at this thread

http://www.tek-tips.com/viewthread.cfm?SQID=867465&SPID=181&newpid=181&page=1

I've posted the code that I use to change parameters in PassThrough SELECT queries at runtime.


You can modify that code to vary the table name etc in an INSERT INTO query.


I'm not sure about 'speed' differences between the approaches but if this is going to be a transfer over and then don't need it again I'd go for this :-

forget the editing queries approach.

You have the tables in your current database and a link to the MySQL target table too

So in VB code you simply write something like

For Each tblName In Tables
DoCmd.Echo True, "Transfering table " & tblName
Docmd.RunSQL INSERT INTO targetTable SELECT * FROM tblName

Next



'ope-that-'elps.






G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Edski]

Nope, I have the little tables in another database, because they are WAYYYYYY toooo B I G. (I lied when I said they were little )

That's why I need the IN Database bit.

Secondly, this will be done over and over and over again. Not just a once of thingy.

And I will also want to READ the data from the MySQL table once I've transferred all the ahem, little ones.

So, still wondering if I should link the tables (like I'm doing now - no problems) or is there a way to run variable parameter queries without the need to link them MySQL tables.

As always, thanks for your continuous and much appreciated help. I learning new things all the time.