I need a “how to” on creating a database connection to an MS Access database. I’ve been able to successfully create the connection but when trying to create a solution based on the connection all kinds of errors start happening. Anyone with experience in the area?
David, if you are still monitoring this post, did you ever find out how to get Servoy to work correctly with Access?
I have managed to get it to see the tables in Access via the JDBC/ODBC driver on a PC, but there are problems if I try to create a form on it.
I also need to be able to get it working from a Mac.
Any help would be appreciated.
Thanks,
Rafi
Best way is to create a link table to it from sql anywhere. See the chapter ‘Accessing Remote Data’ in the ‘ASA SQL User’s Guide’ for detailed info and examples.
jaleman:
Best way is to create a link table to it from sql anywhere. See the chapter ‘Accessing Remote Data’ in the ‘ASA SQL User’s Guide’ for detailed info and examples.
We used same idea except with MS SQL.
HJK said more people might be interested in our experience when using MS-Access as a datasource.
We first tried the MS-SQL remote database connection. On MS-SQL 2000 you cannot ‘see’ the tables in the remote database so you cannot make forms on the remote database.
Using the ASA remote database functionality (first add a generic remote database, then use New->proxy table) you can ‘publish’ your Access databases into the normal table space, and build forms on it as you are used to (do add a row_ident to one column to make servoy happy ).
Hope this helps anyone struggling with the same problem.
Gert-Jan Aaftink
hi everybody,
i try to connect to a access db from servoy.
i can make the connection, but the main problem is, that servoy can’t find any primary key in the tables.
Is creating a link table the only way, to solve this problem?
ww
Jan Aleman:
Best way is to create a link table to it from sql anywhere. See the chapter ‘Accessing Remote Data’ in the ‘ASA SQL User’s Guide’ for detailed info and examples.
This is a 313 Mb download. Isn’'t there an easier way to link to an MDB-file?
After downloading SQL Anywhere 11, at installation time one can choose a custom setup and switch off the components that aren’t needed. Which components are required if the only problem is the linking of a table in an MDB-file?
I’ve tried to connect to an MDB-file, but got no clue how to do it. It’s a pity that there is no easy guide to this, so I can play around with Servoy. The video’s look so promising. I’m loosing precious time and am about to drop my plans and struggle further on the easy-to-manage-but-slow MS Access path.
You could give this driver a try: http://www.csv-jdbc.com/stels_mdb_jdbc.htm
@Jan, if this is the level of support to expect for the future …
Isn’t there a step-by-step tutorial how to get started with an existing mdb-file? That will give all these poor struggling Access-RAD-developers out there (like me) a quick and positive start with your software.
I only believe your story if I see it working on my own data files.
Please show us how easy it really is!
Siem, first of all, this is not Servoy to blame, but MS Access, because it is totally NOT jdbc compliant..
So thinking to start with Servoy and have MS Access as a datasource is a bad idea, IMHO.
the links and tips that are provided here, are more tricks/hacks/workaround to get ms Access to work (but don’t expect miracles!)
We also did a migration of a MSAccess project and we converted the tables first to mySQL, with this little free tool: http://www.bullzip.com/products/a2m/info.php
They have a version for MSSQL as well: http://www.bullzip.com/products/a2s/info.php,
oke for the opensource minded people, a version for postgresql as well: http://www.bullzip.com/products/a2p/info.php
from that point, Servoy is working with a ‘real’ jdbc compliant database..
Siem:
@Jan, if this is the level of support to expect for the future …
@Siem That’s how a forum works, you get compact advice both from the community and from us. If you want to challenge us how we can help you get to your Access files we are more than happy to come over and do this for you!
Siem, there is a webinar tommorow: https://www1.gotomeeting.com/register/839923449
about converting MS Access to Servoy!
Harjo:
… and have MS Access as a datasource is a bad idea, IMHO…
Hi Harjo,
I fully agree, that is the reason I’m considering Servoy, but I need to play around a little with Servoy’s front end RAD, before I put effort in converting my backend and please, understand my critical attitude: Servoy is missing business if they don’t pave the path for the spoiled, draggin&dropping Access-community!
Take care,
Siem
Siem:
Harjo:
… and have MS Access as a datasource is a bad idea, IMHO…Hi Harjo,
I fully agree, that is the reason I’m considering Servoy, but I need to play around a little with Servoy’s front end RAD, before I put effort in converting my backend and please, understand my critical attitude: Servoy is missing business if they don’t pave the path for the spoiled, draggin&dropping Access-community!
Take care,
Siem
Siem I agree. We need to have a step by step MS Access tutorial, we’ll have it before the end of the week.
Harjo, did you catch the webinar in an avi for people that missed it (like me)?
Jan, that’s fantastic!
Siem,
If you just need to play around with Servoy, then move your database to an SQL database. It’s easy to do that with Access. For example, some of the tools available to help you to migrate your Access database to MySQL are:
- Access Export
- Microsoft DTS
- MSSQL2MYSQL
- SQLyog
Or do what I did. Just create a SQL database with MySQL, PostgreSQL or MS SQL.
Do yourself a favor: don’t try Servoy in Access. Trying to make Access work in Servoy is more time consuming than just replicating your database (or part of it) in a SQL database and start building your Servoy solution.
Trust me, I had an Access and FileMaker humongous databases. I tried your path and I failed! Once I tried the full migration, things moved really fast. I build the entire system in two weeks! … of course, I had to improve it for two more months (due to some happy users’ requests)
Steps to connect to MS Access using JDBC can be found here: