ODBC, MS-Access and SC7

Applications Database Connections
,
#1

Hi all,
At the outset, I am a seldom Windows user.

I have a client who needs an application to run on his WIndows based server. He wants to retain the MS-Access (2007) database. The application is relatively simple and an ideal project for a code generator like SC or PHPRunner.

I’d prefer to use SC7, the idea being we’d buy an extra licence just for Windoze/ MS-Access. The current trial version has been installed on a Win7 PC for testing purposes.

I followed this guide on the SC website (http://www.scriptcase.net/docs/en_us/connecting-your-database/access/connecting-access-via-odbc) to create the ODBC connection. I am confident the pre-requisites are in place.

The ODBC connection to the MS Access database I created has been tested and confirmed to be working in PHPRunner and Delphi test programs. However, when attempting to create an MS Access connection in SC7, I get and error:

“odbc_connect(): SQL error: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified, SQL state IM002 in SQLConnect”

I note in step 3 of the guide it shows the ODBC name to be the path to the MS Access database. Is that correct? Although, I have tried it both ways. No luck :frowning:

Any suggestions, or do I bite the bullet and use PHPRunner?

Thanks in advance…

#2

I have successfully connected to an Access 2007 database in Scriptcase.

You need to create a System DSN in the ODBC Data Source Administrator on the Windows machine (not a User DSN). That is the name you put into the connection ODBC Name box in Scriptcase - not the database name and path.

#3

Hi Ians,

can you see both (tables and querys) when you try to create a new grid app ?

because I can’t (V6 after .020 up to last V7 version)
Sc only display tables in the datasource combobox

if you can do this please give me the secret
thanks
Aquiles Rodriguez

#4

I do not have views in my Access database because I build an Access frontend containing the programs/reports/queries separate to the backend data - makes changes easier to distribute.

However after your above comments, I added a view to the Access data file and you are correct - only tables appear in any data source combobox I can find in Scriptcase. I note that the recommended setup is for Access 2007/2010 (accdb) - maybe it was different for earlier versions (mdb).

#5

Hi Ians,

MsAccess have a powerful query designer, and these querys can act as “virtual” tables for SC, this feature turn very easy construct grid app in SC

by example, union querys, querys over another query and/or tables, pivot querys, etc.
if you can do in MsAccess and you can view the result in MsAccess THEN this result act as a simple “virtual” table for SC and the grid app is simple without any complex work.

this world was wonderful up to Sc 6.20 but after these release SC only display tables to choose for a grid datasource, this bug was reported more than a year and nothing change.
the problem is in the msaccess_odbc.exe third party component of SC.

Aquiles

#6

[QUOTE=Ians;24623]
You need to create a System DSN in the ODBC Data Source Administrator on the Windows machine (not a User DSN)…[/QUOTE]

Thanks, that was the issue. I wasn’t specifying System DNS.

#7

I’ve noticed that. A bit of a PITA, IMO.

#8

[QUOTE=aquiles;24691]
this world was wonderful up to Sc 6.20 but after these release SC only display tables to choose for a grid datasource, this bug was reported more than a year and nothing change.[/QUOTE]

Agreed. Maybe the SC team would like to comment?

#9

SOLVED: ODBC, MS-Access and SC7

Thanks for the responses, people.

Appreciated.