Use MS Access as Front End for SQL Server

[Phudsen]

Hi,

I am not the SQL server admin, but I have a database on the server to which I am the owner/admin. When I open the Enterprise manager, I can do everything, create tables, insert data manually. The SQL server uses my intranet login.

I created an MS Access .adp file, connected to the database on the SQL server successfully, viewed the data with no problem, but when I tried to insert data I got a message that the record source is not updatable.

The questions are:
How to connect Access to SQL server and insert data?
What is the equivalent of MS Access Memo field in SQL Server?

Thanks a lot
Paulin

 

[SQLSister]

In order to insert or update data in Access when using SQL Server tables as the back end, each table must have a way to uniquely identify a record, usually a PK.

If you have bit fields you may also, for reasons that are entirely unclear to me, need a timestamp field.

Memo fields are tricky, the can be text, ntext, varchar, nvarchar because the various character fields can accept more than the 255 charcters that a character field in Access can have. I suggest that if you are using ordinary English and expect no foriegn characters, use varch which can accept up to 8000 characters. If you know you are going to need more than that use text.

If you must accept foreign characters, use nvarchar but it will only take up to 4000 characters. Anything over that, use ntext.

Questions about posting. See faq183-874

 

[Phudsen]

Hi SQLSister,

Thank you for your quick reply.

I did not have any PK in the tabes, this might be a reason.

So, using Access Upsizing, I should be able to insert records into the SQL server.

Thank you very much for the expalnation on the memo field.

Thanks a lot
Paulin