SQL Express Linked Tables

[vbdewd]

Greetings Folks,
I could use some advice with table relationships.

Using Access 2002, I just exported my Access tables to SQL Express 2005 database running on remote server. I then linked by "getting external data" which sets up the linked tables as "dbo_mytable" format.

I changed the data sources on my forms/reports to get data from the linked dbo tables.

My Access table relationships were removed in the process.

Should I redo the table relationships in Access or should I set up the relationships on the server as database diagrams?

Many Thanks!

 

[dhookom]

I would have simply renamed the linked tables back to the pre-dbo versions. You can set up the relationships on the server.

Duane
Hook'D on Access
MS Access MVP

 

[vbdewd]

Thanks dhookom,

I did set up the relationships in SQL after I exported the tables to the remote box. It seems to work, however I did have some trouble with a CheckBox field concerning NULL values.

What is the correct procedure for setting up the relationships? Locally in Access or remotely in SQL? Or both?

Thanks Much!

 

[dhookom]

You should set up the referential integrity on the server using constraints. This is probably a better question to ask in the SQL Server forum.

You should make sure you have a default of 0 set for the yes/no field(s). I avoid these fields in Access replacing them with integers.

Duane
Hook'D on Access
MS Access MVP

 

[vbdewd]

I did set the default of 0 for the yes/no fields. That's how I cleared up the problem with the NULL business. I was getting a message that another user is trying to change the table at the same time when I tried to delete a record.

Thanks for your input!