USING CRYSTAL SQL DESIGNER - CREATE TABLE in Access Database

[Mez63]

Hi,
I'm new to Crystal reports and I'm having a hack round to see what I can do.
I'm used to using SQL Server 2000 & Transact-SQL.
I was looking to create a local database (i can't use the SQL Server database as it's a company production version & I can't clutter it up with development data)
In Crystal SQL designer I did just test this function using the SQL server ODBC link. It worked o.k. and I deleted the table I'd created.

CREATE TABLE [TEST_TABLE] (
[PROJ_NO] varchar (10) NOT NULL,
[CRE_DATE] datetime NOT NULL,
[REL_ACT__DATE] datetime NULL,
[STAND_RESP_ID] varchar (10) NULL,
[CO_STAT_CODE] varchar (10) NULL)

So I thought I'd try creating an access database on my local machine. Which I did in 'my documents' folder - test.mdb.
I created 1 table table1.

When I tried the above function after linking to the access database I got an error :-
ODBC Error [Access Driver] Cannot modify the design of table 'TEST_TABLE'. It is in an read-only database'.

Can anyone help.
I'm not an expert in Access I've only dabbled with it. I've played around with creating ODBC links and setting security permissions but I haven't managed to fix the problem.
My machine is running Windows 2000.
Access 2000.
Crystal Desgner 8.0.0.0.

Regards,
Ian

 

[synapsevampire]

Might be your connectivity in part, ODBC connections have options for read only, or if it's opened elsewhere, but I'm not an Access person either.

Anyway, the above code probably won't fly in Access anyway, it doesn't have those data types.

Try something like:

CREATE TABLE MyTable (Company Text, StartDate Date);

-k

 

[synapsevampire]

BTW, not even Crystal suggests using their SQL Designer these days.

-k

 

[Mez63]

The above code was written in the Crystal SQL Designer.
As I say it worked on an SQL Server 2000 database but not Access.
I think I'm reaching the conclusion that the Crystals own SQL function is not that powerful and I'll have to write queries elsewhere to prepare the data for the crystal reporting tool.

Thanks for your help anyway.

 

[synapsevampire]

That is precisely the conclusion I was trying to bring you to.

Since you gave no indication what you were trying to accomplish architecturally, I really couldn't offer much more.

I guess that your saying that the above code worked on an Access database after it was written for SQL Server?

Interesting, but I doubt that it was passing in that form, Access would puke.

Access isn't exactly a big kid database, at least use MySQL, it could probably handle this type of thing.

There is a freebie version of SQL Server that lasts for 120 days that you could set up on your computer for dev.

-k