Microsoft Access database

[vuott]

Riporto questa discussione apparsa sulla M.L.I.:


" Is it possible to connect  Microsoft access database with Gambas?

If yea, please, an example.

Best regards,

Ivan "


" Not that I know of, but I have written a system to convert MSAccess
databases to MySQL/postgresql/MSSQLServer.  It's written in java and
uses the jackcess library (http://jackcess.sourceforge.net/).

I also wrote a server which makes calls to the jackcess library based on
simple text commands, so in theory you could make a basic client in
Gambas...

Let's first see if anyone has already found a way to talk to access dbs
directly in Gambas...

Kind regards,
Caveat "


" I would think you should be able to use the ODBC component to connect ot
MSAccess?

Check this out:
http://office.microsoft.com/en-us/access-help/administer-odbc-data-sources-HA010275550.aspx

And this: http://gambasdoc.org/help/doc/odbc?en&v3

Randall Morgan "

[vuott]

...continua...


" ACCESS db is really just a file ,and not a 'server' like MySQL
You need software to install on your linux box to tickle the file.
There is something out there, I did use something on a box a while back.
(sorry forgot what is was)

nando "


" I thank you all.

I'm running MS Access on the remote machine and I need actually connect to
MS access.
I still have no solution.

Kind regards,
Ivan "


" Simplest solution is to install MSSQL Server (personal version should work)
and follow directions for accessing an Access data store via MSSQL Server
(Lots of info on the net).  Then Connect to the SQL server over the network.
Here's how to setup ODBC for Access and MSSQL Server:
http://office.microsoft.com/en-us/access-help/administer-odbc-data-sources-HA010275550.aspx

Randall Morgan "


" I am grasping at the thin straws of memory here, but for what it's
worth.

I used to do something along these lines, but IIRC:
a) MSAccess did/does not run as a server process, especially to remote
machines.  In order to get it to that we had to use ODBC and establish a
connection process on the "server" and similarly an ODBC client on the
remote "client" machine. In the case of a single connection to the
"server" this was reasonably trivial, but if multiple "clients" were
involved this bordered on a nightmare.
b) the linux/GNU/whatever ODBC libraries we used were extremely basic
but did work within their constraints. In short, scalabilty was a pipe dream.
c) there was/is a community called databaseadvisors(.com) who were a
great deal of help.

Looking at your problem from a distance, using a proper rdms is in the
long term a better road. ymmd.

best of luck
Bruce "

[vuott]

...continua...


" This can't be stressed enough. Furthermore, if Access is actually running
on the remote machine as Ivan said, that counts as a client. If you access
the database via ODBC at the same time as through Access itself, whether
your ODBC client is VB, Gambas or anything else, you risk corruption. Like
sqlite, Access is designed for single-user data storage, even if Microsoft
has added features over the years to make it seem otherwise.

I did a project exactly like this about 8 years ago, and we ended up
moving the data into a MySQL server, converting the tables to ODBC links
using some Access macro we found in a MySQL forum, and accessing the MySQL
data natively from Gambas and a web inquiry system. Even so, Access
misbehaved constantly and made it a nightmare.

Multi-user networked database applications need a multi-user networked database
server. Period.

Rob "


" This is true, You should not change your access database while connecting
to it via ODBC. Also, you may find some queries don't work as expected.
Again, Access looks like a T-SQL database but is not really. It would be
best to use the ODBC connection to update a local MySQL or Postgres
database on your Linux box. Not only would it be faster, but much more
reliable. If you provide access via odbc through MSSQL Server and not via
MS Access then, the issues with multiple clients and updates shouldn't be
an issue as MSSQL manages the connections and data source.
Randall Morgan "


" Ready to take another look at converting your multi-user database (ahem!
SINGLE USER FILE!) to a proper dbms? :-D

I did also look at mdbtools and the like before rolling my own
conversion tool but nothing worked particularly well... I ended up with
some fugly combination of awk, sed, grep, etc. and it still didn't do
the conversion right!

If your data isn't particularly sensitive nor particularly huge, send it
on and I'll see what my conversion program makes of it. Right now, I've
converted the database we use at work for stock control, invoicing etc.
to both MSSQLServer and MySQL and of course, the ubiquitous Northwind
sample db.  I'm still planning on adding support for binary data
fields... if you don't have any, then you're in luck!

Kind regards,
Caveat "