Access 2000 project

[jewel]

I have started a new project - ODBC connection
This is the first time I have used the project side - so was surprised to find no query window. Moving on I see I need to do relationships in the Diagram window.

I have a call table - primary key (field CallNumber)
and a worktime table - (has a field CallNumber)

that I am trying to link together - error reads
ADO error: ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint

I'm not sure I understand what is wrong?

This is a lead up to doing reports using MS Visual Studio.net but I don't know enought sql code to do it directly. I also have MS SQL Query Analyser but felt if I could follow thru in Access it would be better for me to understand.

thanks

 

[jewel]

sorry everyone I didn't realise there was a project forum - so I have posted this one in there instead

 

[BSman]

I looked at Access project some time ago and decided not to use it (with a SQL back end) for the following reasons:

1. Although Access 2000 lets you create and modify the SQL Server tables, views (queries), stored procedures, my understanding is that the newer versions of Access do not permit you to do that. So you would need to also go in to SQL Server and change them there. This means that you would not have access to the value of controls on an Access form or report, limiting the ability to do some things "on the fly".

2. All tables and views/queries must be in the SQL Server database. You cannot set up any "local" (i.e., Access jet engine) tables or queries that you might want to use for application control or temporary queries.

If you have full admin access to SQL Server with the full set of tools (as in Enterprise manager and SQL Query analyzer) and the knowledge to use them properly, then you might be fine. Otherwise, I would suggest using the regular Access front end connected through ODBC to SQL Server. In that method you have access to any views defined in SQL Server by using Access pass through queries.

 

[jewel]

thanks BSman for your reply.

I have decided to bite the bullet and only use SQL Query Analyser and Studio, a very sharp learning curve but have already spent a few hours, certainly a little easier with an Access background, but still a lot to learn. Yes I have the full rights, so am spending my next few weeks trying to come to terms with how it all works. No doubt I will have a few questions to post to the sql forum lol.

Thanks for your advice.

cheers