Can MySQL be used with Access as a *desktop* backend?

[PW]

I am getting worried that the attachment database that I am using just
for storing PDFs, Word documents, scanned images, etc... is going to
eventually hit the 2GB database limit (Access 2010).

So I am trying to think of an alternative if I need one.

And how would it work using MySQL as part of the installation setup? I
guess I might have to use remote desktop to install it and whatever
else is needed (Apache server??) in order to use MySQL as a partial
backend?

Over my head! :-)

-paulw

[David Hare-Scott]

About MySQL I have no idea. But if you have more than one table in your
attachment database you can move them into separate MDBs/ACCDBs down to one
table per database. You can have many linked databases and as long as the
table names don't change or you don't need to split a table over two or more
the FE will never know the difference. So the real limit is 2GB per table.
Your FE-BE relink code will need an update of course.

David

[PW]

On Sun, 7 Apr 2013 08:04:17 +1000, "David Hare-Scott"
<sec...@nospam.com> wrote:

>PW wrote:
>> I am getting worried that the attachment database that I am using just
>> for storing PDFs, Word documents, scanned images, etc... is going to
>> eventually hit the 2GB database limit (Access 2010).
>>
>> So I am trying to think of an alternative if I need one.
>>
>> And how would it work using MySQL as part of the installation setup? I
>> guess I might have to use remote desktop to install it and whatever
>> else is needed (Apache server??) in order to use MySQL as a partial
>> backend?
>>
>> Over my head! :-)
>>
>> -paulw
>
>About MySQL I have no idea. But if you have more than one table in your
>attachment database you can move them into separate MDBs/ACCDBs down to one
>table per database. You can have many linked databases and as long as the
>table names don't change or you don't need to split a table over two or more
>the FE will never know the difference.

Splitting a table to more than one DB isn't possible right? I guess I
could have "Evidence A-K" and "Evidence L-Z" if I had to?

Two tables in the database. One for this and one for that. One may
end up being bigger than the other one. Depends on the user I
suppose.

> So the real limit is 2GB per table.
>Your FE-BE relink code will need an update of course.
>
>David

Thanks David!

-paulw

[Albert D. Kallal]

"PW" wrote in message news:g1i0m8tvcmo8ed9g0...@4ax.com...

>I am getting worried that the attachment database that I am using just
>for storing PDFs, Word documents, scanned images, etc... is going to
>eventually hit the 2GB database limit (Access 2010).

The problem is MySql does not have a attachment type column.

In fact, most server database systems do not.

I would suggest that you export (remove) the attachments from being stored
inside the database, and ONLY store a path name.

Such a setup would allow you to EASY store 1 million rows with room to spare
since the documents etc. would NOT be stored in the database.

So, only store the path name in the database, not the actaul file.

best regards,


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
PleaseNoS...@msn.com

[Access Developer]

No, you can't split one Table (Access object) between two or more backend
databases; but, as you suggest, you can put the data in two different
tables, each of which can be in a different backend database. A caveat,
however: that's not "normalized" good relational design, so in the long run,
it is likely to cause you problems.

You can use any ODBC-compliant backend database... MySQL is a commonly-used
one, likely to be supported by websites (but can be installed on the same PC
as the front-end, if you prefer). PostgreSQL is a database that many prefer
because it more strictly follows relational database design principles.
Another caveat: I can only report what others have told me, because I have
not used either of these.

Something over half the database work I've done for pay used a separate
backend DB, among which were Informix*, Microsoft SQL Server, Sybase SQL
Server, and Sybase SQL Anywhere. I did not install nor maintain any of
those, as the client company had Data Base Administrators for the one(s)
they used.

* Informix was purchased by IBM some time ago,
and I don't know if it is available as a separate
product, now.

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

"PW" <emailad...@ifIremember.com> wrote in message
news:2cr1m8lt5ir1e7ktr...@4ax.com...

[Patrick Finucane]

I second Albert's advice

[The Frog]

What Albert said, or use blobs in MySql for storage. I have moved
several BE structures to MySql, and I use the OLE driver not ODBC. No
probs at all. Good for replicating between sites too, or placing
online with a web interface.

--
Cheers

The Frog

[Bob Quintal]

"Albert D. Kallal" <PleaseNOS...@msn.com> wrote in
news:Ho68t.23708$5N5....@newsfe20.iad:

> "PW" wrote in message
> news:g1i0m8tvcmo8ed9g0...@4ax.com...
>
>>I am getting worried that the attachment database that I am using
>>just for storing PDFs, Word documents, scanned images, etc... is
>>going to eventually hit the 2GB database limit (Access 2010).
>
> The problem is MySql does not have a attachment type column.
>
> In fact, most server database systems do not.
>
> I would suggest that you export (remove) the attachments from
> being stored inside the database, and ONLY store a path name.
>
> Such a setup would allow you to EASY store 1 million rows with
> room to spare since the documents etc. would NOT be stored in the
> database.
>
> So, only store the path name in the database, not the actaul file.
>
> best regards,
>

I agree. I take Albert's principle even further, in that my code to
create an attachment copies the file to a sub-directory of the
database, renames it to the row's id (keeping the extension), then
sets the file properties to read-only.

Since the path is constant, I don't even need to store it in the
table..



--
Bob Q.
PA is y I've altered my address.

[PW]

On Sat, 6 Apr 2013 22:14:33 -0600, "Albert D. Kallal"
<PleaseNOS...@msn.com> wrote:

>"PW" wrote in message news:g1i0m8tvcmo8ed9g0...@4ax.com...
>
>>I am getting worried that the attachment database that I am using just
>>for storing PDFs, Word documents, scanned images, etc... is going to
>>eventually hit the 2GB database limit (Access 2010).
>
>The problem is MySql does not have a attachment type column.
>
>In fact, most server database systems do not.
>
>I would suggest that you export (remove) the attachments from being stored
>inside the database, and ONLY store a path name.
>
>Such a setup would allow you to EASY store 1 million rows with room to spare
>since the documents etc. would NOT be stored in the database.
>
>So, only store the path name in the database, not the actaul file.
>
>best regards,

Good point Alex. I think I will come up with a document management
system that will do just that.

-paulw

[PW]

I don't know what a Blob is Froggy, and I also do not know what the
difference is between using OLE and ODBC (and how to use OLE).

Are you saying that I could possibly upload "availability" to MySQL on
a web server and also download data from the table(s) using a desktop
version of Access 2010?

Thanks!

-paulw

[David Hare-Scott]

PW wrote:
> On Sun, 07 Apr 2013 22:57:01 +1000, The Frog
> <Mr.frog...@googlemail.com> wrote:
>
>> What Albert said, or use blobs in MySql for storage. I have moved
>> several BE structures to MySql, and I use the OLE driver not ODBC. No
>> probs at all. Good for replicating between sites too, or placing
>> online with a web interface.
>
>
> I don't know what a Blob is Froggy, and I also do not know what the
> difference is between using OLE and ODBC (and how to use OLE).
>

A BLOB is a BinaryLargeOBject. A container for whatever kind of object
(spreadsheet, Word Doc etc) you like.

ODBC is OpenDataBaseConnectivity, it is a quite old mechanism for linking
FEs of various kinds (including Access) to BEs of various kinds (including
JET/ACE). For example Access used to be supplied with ODBC drivers for
FoxPro, SQL Server, Paradox, etc.

OLE used to mean ObjectLinkingandEmbedding but now it means whatever
Microsoft want it to mean that may have nothing necessarily to do with
linking or embedding. So we had OLE automation that is now just called
automation, where one app can control another or use features from it. Also
we have OLE servers, so Access can be linked to (say) SQL Server via ODBC or
via OLE. These are quite different systems with different purposes yet
connected by the same name; in the MS marketing world names are malleable.
In this context OLE is an alternate mechanism for linking a FE and BE. The
structure, features, performance and syntax of operation using one mechanism
will be different to the other, so one may be preferred over the other
depending on the situation.

David

[PW]

Good stuff David! Thanks! But can I do what I want to do
(synchronizing with a MySQL or Access backend located on a web server
from a desktop Access Front End w/o having to rewrite my software as a
Sharepoint application or going the way of Amazon Web Services -
Remote Desktop - GoToMyPC?).

-paulw

[The Frog]

Hi PW,

Yes, you can use Access as a front end to MySql either locally on the same machine or across a network including online. What you need to take into consideration is how your application is going to work, what are its data needs. The trick to working with online databases is to 'keep the wire cold'.

If you create a connection to an online MySql BE you will have a fiarly small pipeline to push and pull data too and from. On top of which you can have connectivity issues so you need to be able to have your application work in a 'stateless' way - ie/ connect only when needed and do only what is needed verifying that it was actually done (in short).

Another way of handling this is to use replication in MySql with what is called a multi-master type setup. This means that when the locally installed copy of MySql can see the online one data is synchronised between them. You can do this as well with MS SQL Server.

Another way would be to have a WSDL based web service (ie/ a web page that your program uses to exchange data with an online data source) connected to your database BE. You could then in theory have updates / synchronisation done between the two. Alternatively you could make your online database the only one you use and the application might only store some information locally in an MDB / ACCDB until is can push / pull the info as needed. Or some hybrid of the two.

In short there are many ways to achieve what you are talkign baout but the critical questions you need to ask are:
1/ What is the 'right' design for the application and its needs?
2/ What are the options to achieve that design?
3/ How can the application be built and supported such that it continues to work even if I am no longer there to do so?

The more planning you are able to put into this the better the result will be. I am a big fan of having a MySql BE for my different projects due to the availability of the LAMP stack (Linux Apache MySql PHP) at web hosting services and that the software is free (in the open source sense). You can grow your application effectively without restriction. Need a web interface for it? No problemo. What about working with a mobile phone? Sure. How about EDI with third party companies? Not an issue. You get the idea. Again I must stress that the questions asked above are critical to success. By all means experiment away with the different technologies to familiarise yourself with them, but dont make the mistake of thinking that you can simply take any app, be it in Access (which I love) or any other platform, and simply dump it to the web. Even in SharePoint this is not possible despite some of the hype.

I hope this helps you in deciding how to approach your situation. There are rewards to be had by heading down the web based database path, but you must also learn the skills to effectively use such a powerful tool.

Cheers

The Frog

[PW]

Good stuff. I will get back to you tommorow. Mark you calendar :-)

Thanks!

-paulw

[The Frog]

Your timing is good PW as I am working on a MySql BE with multiple
client type FE's that should be able to go to the web when I finish
building it. Clients need to work on and off-line. Pretty much what
you're asking about I think.

--
Cheers

The Frog

[PW]

Pretty much exactly! :-) I have an account at BlueHost and have
already created a database and tables for a project that didn't
happen. I can practice there.

I read the Sharepoint will synch off-line and on-line, but I don't
want to deal with a rewrite. Maybe I can just rewrite a couple things
but I am not sure.

Thanks.

-paulw

[PW]

Are you doing the front-ends with Access (my guess, aren't I smart
lol!).

I also am curious if Dreamweaver or something like BootStrap could be
used. If I can get this working and come up with something that
clients can use for on-line reservations and the owners testing their
stats, I will probably have to style the pages to match their websites
(specifically the former). My CSS knowledge really isn't knowledge
(but I still can learn)! :-) Whatever I come up with will no doubt
have to be mobile friendly/adaptive/responsive or whatever the word or
the day is!

-paulw

[The Frog]

Hi Paul,

Yes I am using Access (2010 32 bit) as the first front-end for this application / system. There is a need to have it up and running fairly quickly and Access is perfect for getting the job done quickly and reliably. I think it makes a great framework.

This application will also be (later) having interfaces that are both app based and web based. One of the reasons that I am using MySQL is that availability of the LAMP stack that I mentioned previously. I will end up with an HTML based interface to the database and its functions, most likely written as an HMTL5 / PHP application that can work both on and off-line. There is also a good chance of Android, Windows mobile, and Apple apps as well. One thing at a time though.

In order to do what you are talking about it requires a good deal of planning. I usually start with a very broad look at the general requirements of the system, and then start drilling into those requirements to flesh them out in GREAT detail. From this you can then start dealing with a table structure for an online (ie/ always connected) application like you normally would with Access.

Once you have the basics done in terms of table structure you can see (and test) where the data push and pull is going to be. This is an essential step to do because I find it help in understanding where the load is on a system and on a particular design. I then end up crafting a series of stored procedures that take care of the push and pull. This essentially means that your Access application no longer needs the linked tables. Some will disagree with me here on doing this as Access is intended to work with tables and queries. Unfortunately for Access the web is not, and herein lies the divergence from 'normal' use of Access to a more disconnected approach.

In designing your forms and reports it is necessary to base them on recordsets rather than on underlying tables or queries. When you have a situation that for example has multiple lookups and would involve multiple round trips to the BE for information you will find it probably easier and faster (not to mention more reliable) to keep a locally cached data set (temp tables, recordsets, arrays or whatever is most suited) and run the query that underpins the form or report locally. If changes are made to the data you can then send them back to the BE (using an appropriately designed stored procedure that accepts the data you are sending).

As an example I have a simple stored procedure in MySQL that pumps some info to a table and returns the row id so that I can use it for pusing data into the related table for child records (as the fk value). This stored procedure looks like the following (in MySQL):

DELIMITER $$

CREATE PROCEDURE `DataBaseName`.`procProcedureName` (txtCriteria VARCHAR(100), txtLocation VARCHAR(50))
BEGIN
START TRANSACTION;
INSERT INTO tblTargetTablet (Criteria, Location) VALUES (txtCriteria, txtLocation);
COMMIT;
SELECT last_insert_id() as row_id;
END

This takes two strings, pushes them into the table and gives back the row_id (pk of the row the procedure just made) to whatever called it. The row_id is returned as a result set.

FWIW this is a test scenario I am playing with that could potentially benefit from the use of a third parameter being passed in that is in turn pased back from the stored procedure with the row_id value in it. Stored procedures in MySQL support IN, OUT and INOUT for parameters (default being IN). Cant seem to get the OUT parameter to work only with the ADO connection object so I am using a MySQL result set -> ADO.Recordset.

The VBA code to produce this operation successfully:

Sub test2()
Dim strCon As String
Dim db As ADODB.Connection
Dim rs As ADODB.Recordset

Set db = New ADODB.Connection
Set rs = New ADODB.Recordset

strCon = "Driver={MySQL ODBC 5.2a Driver};Server=localhost;Database=MyDataBase;UID=InsertUserNameHere;Password=InsertPasswordHere;Option=3"

db.Open strCon
db.procNewSensisDataProject2 "Criteria Goes Here", "Location goes here", rs

rs.MoveFirst
Debug.Print rs.Fields(0).Name & ": " & rs.Fields(0)

db.Close

If Not rs Is Nothing Then Set rs = Nothing
If Not db Is Nothing Then Set db = Nothing

End Sub

Now obviously the code examples I have provided above dont have error checking or any other real structure to them but they should be sufficient to give you an idea of how to approach the scenario. You can also use a Command object to work with parameters if you need to.

In the case where you want to grab a chunk of data and populate a form with details you might need several stored procs to achieve what you want. You may also need to work with OUT parameters (meaning until I discover otherwise working with the Command object) and recordsets at the same time in the same procedure. The procedure for example might update a table, return the updated table rows child table results, and provide an error code you app can decipher to determine if all went well or not.

By carefully designing your application you can achieve great functionality and reliability and speed - normally three things that dont all play well together. Each scenario is different and what is needed for you will probably be different from what is needed for me, but the tools are basically the same.

In terms of Dreamweaver, I cant say. I was trained a long time ago to work with CS5 but I dont actually use it. I work with Netbeans 7.3 currently for HTML, Javascript, CSS, PHP work. I find the integrated webkit browser great for testing any pages I design on different resolutions (you can emulate a tablet, phone, etc... from a toolbar). It also costs nothing and is so fully featured as to make Adobe's asking price for Dreamweaver a crime. Did I mention MySQL integration too? If you are going to download it grab the PHP version as it has everything you need for web development.

Another thought to consider. If you are wanting to place the database on the web, you may find yourself placing a WSDL 'service' between anyone attempting to access the database and the db itself. Working with WSDL services is similar to stored procedures in that you make calls to it like calling the stored procedure directly. The advantage here is that web browsers can make the calls too. AJAX is a common way of pushing and pulling data across the web. The WSDL service acts like an API to your BE database. Behind the scenes all you really want it to do it to call the databases stored procedures and hand back the results they themselves normally would, just wrapped up in html/xml. This provides you true portability, but you can work up to that by starting with Access and some stored procs to get your design working and then keep building on it from there. If you write a good code base you can port it most likely with relative ease to other platforms such as HMTL5 applications or Andoid / Apple / Windows apps because you logic will be well structured and your data handling is abstracted from the app itself. With a WSDL you can even change the back end database from MySQL to anything else - all the apps see is the WSDL service.

[PW]

Oh my Gosh, this is so over my head! But I love learning new things.

I was thinking all I want or need to do is have the user press a
button to upload availability, and one to check for new reservations
(and also come up with an import module/form). I guess I would have
the reservation availability databases hosted at a place like BlueHost
(which offers MySQL and I've already messed with creating a database
and tables there).

I am still *studying* your last two replies. So I probably will get
back to you (warning! :-)).

Thanks so much!!

-paul

[The Frog]

Hi Paul,

If I understand you correctly then you are wanting to host the bulk of your application locally on the client machine and do a data exchange to keep things up to date. This is commonly known as Electronic Data Interchange (EDI). I can give you a hand with this if you like.

Do you have an example of the txype of data you are needing to exchange?

Cheers

The Frog

[PW]

On Tue, 16 Apr 2013 19:32:25 -0700 (PDT), The Frog
<mr.frog...@googlemail.com> wrote:

>Hi Paul,
>
>If I understand you correctly then you are wanting to host the bulk of your application locally on the client machine and do a data exchange to keep things up to date. This is commonly known as Electronic Data Interchange (EDI). I can give you a hand with this if you like.

Not familiar with that but seems like it's what we want to do.
Probably no credit card processing as that has wasted enough of our
time already. Then there's the security issue that I don't really
want to have to deal with.

>
>Do you have an example of the txype of data you are needing to exchange?
>
>Cheers
>
>The Frog

Well, people keep asking about on-line reservations, coordinating
their availability and make it possible for people to check and make
reservations (I'd rather talk to a real person but I guess that it's
what it is these days). We keep asking them to have their web
designers to give us a call but nobody ever does. I guess they are
just "brochure" website designers rather than working with data. Just
pretty websites.

I have not thought out the table structures as I was not sure if
snychronizing a desktop Access 2010 application with MySQL or Access
on web server would be possible (without using SharePoint...).

The tables probably will just have fields like reservationid,
reservation from date, reservation to date, location, client name and
clientid. Stuff like that. I will get together with my wife tomorrow
and see what she thinks. She does most of the talking to our clients
(I am mostly just the dweeb <g>).

I would write an import module to generate reservationids (as that
field in our reservation table is an autonumber field and primary key
so there cannot be duplicates). Shouldn't be that big of a deal
adding new records to the client and reservation tables.

Some clients also want to have reports and forms that others not in
their location can view.

I have seen our application run on Amazon Web Services but can't
afford to hire someone right now to get it done. I've just started to
look into seeing if I could do that myself. That would be a different
market/project than on-line reservations.

Perhaps synching with services like Orbitz, etc... may be okay if that
was even possible but I doubt it as our clients are fishing and
hunting lodges, not hotels.

I don't think scanning an Outlook email would be what most clients
would want us to do.

Thanks!

-paul (paul at
williamson
enterprises
dot com)