Access Front End and MySQL Database

[coyote]

Until recently I was using Access as a front end and back end.Then I discovered MySQL but still want to maintain my access customized interfaces.
I have done all the connections and every thing works perfrect.
Question is
I have one machine which is the local host.How do I migrate the MySQL database to a server in a clients office for me to connect my Access front end.
This was easy when using access as a back end because all I used to do was copy the back end in a shared folder on the server and install the front ends in the work stations

 

[Banana]

The server just need to have MySQL installed and the port 3306 (the default- unless you changed it) opened for the daemon to listen on. You would probably want to ensure that the server's IP is static. Your Access application would then use that IP to 'find' the server. (In MyODBC, where you would have had entered 'localhost' or '127.0.0.1' for Server, that's where the public IP would go).

Did that help?

 

[coyote]

Thanks Banana for a quick reply.
My question is this I don't know where the database is located in MySQL since I can't find any interface. Does this mean I have to redo all the export on my tables from access on my clients machine or how do I package the database.

Also in my Access front end I had set up Custom security where users log in, but there is this Pop up form from ODBC that keeps on popping up every time I try to connect. Is there a way to hide it once a connection is made

 

[Banana]

I'm not sure what you mean you don't know where the database is located.
If you mean you don't know what the database name, then how did you link to the database as it's required as part of ODBC parameter? Or do you mean you don't know where the MySQL client is located? Normally, when you install MySQL, it comes with a CLI client that you can use. Open a command prompt and enter 'mysql' and see if you get something. (It may require a username and password, but for now just see if it's there).

Alternatively, you can use Access to give you MySQL commands- open a new passthrough query and use those SQL command:

SHOW DATABASES;
SHOW TABLES:

(the second command will tell you the name of database.)

As for the second question, you can create a custom form to log in the username and password and concatenate with the ODBC string or store it (understanding that there is security risk involved).

Did that help?

 

[NigelShaw]

Hi

In reference to mysql interface, you need something like toad to access the tables and queries.



Nigel

 

[Banana]

Hey Nigel- been a while.

Anyway just to clarify a small point- toad is certainly useful in accessing tables on MySQL but its not the only tool and if the OP will be doing of work in Access and/or the schema is reasonably simple then the built-in CLI mysql client will be sufficient and then there's free GUI tools provided by MySQL, noticably MySQL Administrator.

Toad (I've used Navicat) certainly a great and easy to use tool and quite worth the cost when we get into complicated stuff with MySQL.

 

[coyote]

NigelShaw whats toad?


Thanks Banana, I know the name of the database my question was if am to package this database since it has all the tables and access is the front end how do I copy it so that it can run on my clients machine.
Which file do i copy to be exact.
At least I can see the front end.
Cheers

 

[Steve R.]

Very timely and useful post. Last week I got MYSQL working on my LINUX computer (Ubuntu), but I have not yet had time to figure out how to connect to MYSQL with ACCESS. In researching the issue, I did run across "Using Microsoft Access with MYSQL". As of yet, I have not tested this approach out - had to spend my weekend doing house projects - instead of resolving this issue.

 

[NigelShaw]

Hi Guys / Banana

Yeah been otherwise engaged on other work stuff but been poking my nose around for a bit to see what's going on.

I use mysql for my websites but it didn't come with a built in ui. I could do a basic setup bit no table creation. It could actually be really useful for me connecting with access as lots of data is stored there.

I find toad very good for whatever use.

Regs

Nigel

 

[Banana]

Coyote,

Toad, like Navicat or phpMyAdmin is a GUI manager for MySQL (and other databases as well). I've heard good things and they simplify tasks that would be tedious on the command prompt, especially on user permissions.

As for copying, I'd use mysqldump. It will then generate a sql file that then can be executed on the other server. You theoretically could just copy the folder with database name contained in data folder but its not 'official' method.

SteveR

Looks like you've found my #2 go-to reference for MySQL/Access. (The #1 would be Jet/ODBC connectivity whitepaper) but there's #3 too- google for 'PostgreSQL long FAQ Microsoft Access'. This FAQ is very detailed and although for PostgreSQL, still has lot of appliciabilty for MySQL.

And of course if you get stuck you always can ask for help either here or MySQL forums.

Best of luck!

 

[DwayneC]

I have spent many hours over the past two or three weeks trying to use MySQL on a remote server as the back-end of a database and Microsoft Access as the front-end on local desktops or laptops. The main table I want to store on the remote host has almost 1000000 records, and even zipped exceeds the host limit for upload, 51.5 MB. I was able to export the structure I want with no problems, and most of the time was spent, with multiple emails exchanges from the host support people, to find an efficient way to upload the big file. It is public information that is updated once per week and is downloadable as a zipped text file; it can also be downloaded as three non-overlapping zipped text files by congressional district. I don't need to update individual records in this file -- just do a bulk update once per week. If anyone is interested I will post the details on how to do this.

The problem I have now is getting information from the MySQL file back to Access using a linked table. When I link the table and use Design view, the structure is properly displayed. However, if I try to query the linked table or just open it in datasheet view, all cells display #Deleted. Further compounding the mystery, I can actually import the file into Access and all data are passed correctly. Of course, this negates the whole purpose of uploading the data to the host. I have set up the ODBC to return matching rows.

I would very much appreciate any help anyone can give.

 

[Banana]

Here's a starter article.

Generally, we get #Deleted whenever Access cannot locate the primary key.

Few possible approaches to fix it.

1) Ensure that your query includes a primary key even if you don't need to read it.
2) Use a timestamp column.
3) Execute a DDL to create pseudo-index locally.

CREATE UNIQUE INDEX ON <YourTable> (<YourPrimaryKey>);

See if it helps.

 

[DwayneC]

Banana,
thanks for the response. I already have a primary key in the MySQL table. However, when I actually import the file instead of linking to it, and then look at the design view, it is not shown as a primary key. In fact, NONE of the indexed fields are shown as being indexed. On the other hand, if I link the table and then look at it in design view, the correct field is identified as the primary key and the other indexes show up in their field properties as being indexed with duplicates allowed.

I also added a timestamp column and forced it to be initialized with yesterday's date. Made no difference.

I do have one small table that links properly -- it was uploaded using NaviCat, I think. It uses MyISAM, but the other tables I have use InnoDB. I copied one of those to a new table, test, and changed it to use MyISAM. It still doesn't link properly.

As a rank newbie, I have no idea what DDL means.

 

[Banana]

The importing process you describe sounds right to me. Importing it in doesn't carry over the indices or the key; it's left up to you to define those for importing them.

DDL = Data Definition Language, which was the SQL I just gave you. It's simply a subclass of SQL where you provide the definition for the tables, views, indices etc. Your other queries fall in the other class of DML = Data Manipulation Language.

To execute this, just open a new query, then click View -> SQL View and enter the command I gave you then run it (click the red ! button). It will appear as if nothing happens but it'll create a new pseudo-index that may enable your table to be updatable.

What happens when you open the table directly and edit some values in there?

It doesn't matter which storage engine you use in this context.

Also, what is the version of MyODBC you're using? What options did you have turned; you already mentioned Return matching rows; what else?

 

[DwayneC]

Thanks for your patience. I am connecting to my website host, Bluehost, and have set up MySQL ODBC 3.51 Driver as a System DSN, BluehostODBC, with port 3306. The following are enabled under the Flags 1 tab:
Return Matching Rows
Allow Big Results
Change BIGINT column to INT
Enable Auto Reconnect.

No boxes are checked under Flags2,3, or 4.

Bluehost is running
phpMyAdmin - 2.11.9.4

• MySQL client version: 5.0.75
• Used PHP extensions: mysql

I can't seem to find the proper punctuation to use for the SQL command -- everything I have tried gives a syntax error. Also, do I need to have the link already established?
Here is one variation I have tried (with and without spaces between the table name (test) and the column name for the primary key (RegNum).

CREATE UNIQUE INDEX ON test (RegNum);

 

[Banana]

Hey,

I think I misled you- I had forgotten you need a name for Index.

Try this:

CREATE UNIQUE INDEX <NameOfIndex> ON test (RegNum);

You can just call it something like "MyPrimaryKey" or use Access's default assigned: "__uniqueindex". Name isn't really important but it's required for syntax.

So sorry to forget that tidbit.

 

[DwayneC]

No problem -- I was able to execute the revised statement -- but here is the result:
Primary key already exists. (Error 3283)Primary key already exists. (Error 3283)

You are attempting to define a second primary key for this table. Each table can have only one primary key. Remove the reference to the second primary key.

Here is the statement: CREATE UNIQUE INDEX MyKey ON test (RegNum);

 

[Banana]

Okay, so we know that Access know what the primary key for that table is. That's one less place to look into.

Did you already try opening the table and editing some values in there? What happen when you edit a couple of rows in there? (If needed, be sure to write the old data back).

 

[DwayneC]

When I try to open the table, all cells show up as deleted, and I can't seem to edit anything.

 

[Banana]

Hm.

If you download MyODBC 5.1 and use that instead (remember to create a new DSN as well) does it help the matter any?

I've seen #Deleted for individual rows but not a whole table.

Also, some more reading materials. It has a section about how #Deleted are caused and how it can be fixed. See if there's anything in there we've not tried yet.