Access Application For adding/editting SQL Records

[Steve]

I have zero experience with ODBC. If I have an Access frontend connected to
a SQL Database using ODBC, are the tables connected like a frontend/backend
Access database where the the tables you see in the frontend are only
"copies" of the tables in the backend? Or ar the tables "real" tables
connected to the SQL tables in some manner? If the latter, can an Access
table have more fields than the SQL table it is linked to?

The reason for my question is that I need to create an Access application
that can add new records and edit existing records in a table in a SQL
database. The records in the SQL table are related to three other SQL tables
but no data entery is needed in these three tables. So the Access
application needs to include all four tables but only provide for data entry
in one of the tables. The interrelationship between the four tables is very
poor and makes data entry to the one table very messy. Adding a couple of
fields in one of the tables and a new primary key in another table in the
Access application, would make data entry very simple. This means though
that the tables in the Access application are not duplicates of the
corresponding SQL tables. Can this be done this way?

Thanks!

[Rick Brandt]

Steve wrote:

I have zero experience with ODBC. If I have an Access frontend
connected to a SQL Database using ODBC, are the tables connected like
a frontend/backend Access database where the the tables you see in
the frontend are only "copies" of the tables in the backend? Or ar
the tables "real" tables connected to the SQL tables in some manner?
If the latter, can an Access table have more fields than the SQL
table it is linked to?

You seem to be making a distinction without a difference. In both cases the
links are connected to the real tables in the back end. It does not matter
if it is an MDB link or an ODBC link. That being the case then no, the
number of fields in the link is always the number of fields in the remote
table.

The reason for my question is that I need to create an Access
application that can add new records and edit existing records in a
table in a SQL database.

Then us an ODBC link.

The records in the SQL table are related to
three other SQL tables but no data entery is needed in these three
tables.

So don't link to those tables.

So the Access application needs to include all four tables
but only provide for data entry in one of the tables. The

Why does it need to link to all four if you wil only be editing one? If you
want to SEE data from the other tables then this would make sense.

interrelationship between the four tables is very poor and makes data
entry to the one table very messy.

You will have to explain this statement.

Adding a couple of fields in one
of the tables and a new primary key in another table in the Access
application, would make data entry very simple. This means though
that the tables in the Access application are not duplicates of the
corresponding SQL tables. Can this be done this way?

You cannot add fields to a link. You could have a separate local table in
the Access file that relates to the link one to one and use that for the
additional fields.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Steve]

Thanks, Rick, for the quick response! Your response answers my question
completely.

"Rick Brandt" <ri*********@hotmail.comwrote in message
news:hd*******************@newssvr11.news.prodigy. net...

Steve wrote:

>I have zero experience with ODBC. If I have an Access frontend
connected to a SQL Database using ODBC, are the tables connected like
a frontend/backend Access database where the the tables you see in
the frontend are only "copies" of the tables in the backend? Or ar
the tables "real" tables connected to the SQL tables in some manner?
If the latter, can an Access table have more fields than the SQL
table it is linked to?

You seem to be making a distinction without a difference. In both cases
the links are connected to the real tables in the back end. It does not
matter if it is an MDB link or an ODBC link. That being the case then no,
the number of fields in the link is always the number of fields in the
remote table.

>The reason for my question is that I need to create an Access
application that can add new records and edit existing records in a
table in a SQL database.

Then us an ODBC link.

>The records in the SQL table are related to
three other SQL tables but no data entery is needed in these three
tables.

So don't link to those tables.

>So the Access application needs to include all four tables
but only provide for data entry in one of the tables. The

Why does it need to link to all four if you wil only be editing one? If
you want to SEE data from the other tables then this would make sense.

>interrelationship between the four tables is very poor and makes data
entry to the one table very messy.

You will have to explain this statement.

>Adding a couple of fields in one
of the tables and a new primary key in another table in the Access
application, would make data entry very simple. This means though
that the tables in the Access application are not duplicates of the
corresponding SQL tables. Can this be done this way?

You cannot add fields to a link. You could have a separate local table in
the Access file that relates to the link one to one and use that for the
additional fields.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com