Embed SQL Server Table in Access Form

Question

HI,

I love the functionality of MS Access tables embedded in Access forms via a sub-form.  I like being able to Add / Delete / Edit data directly into the tables from the forms\sub-forms.

My question is:

Can I get this same functionality using ODBC connections to SQL server tables (being able to Add / Delete / Edit data directly into the tables)?

What would the SQL server Login and user rights have to be to do this?

Many Thanks

Kody_Devl

Answer 1

The performance of delete should not be ANY different here then your existing setup.

If you move your data to SQL server, and link the tables, then your sub form and editing as you have now should work and it will not require ANY changes to your code or even require ANY code at all.

When you have tables on SQL server, and you link to those tables, then your classic Access forms and even those sub forms should allow adding/editing/deleting JUST LIKE BEFORE you move that data to SQL server. Deleting a row will not behave any differnt to your users - and this includes performance - it should be instant.

For the most part your forms should work as before.

Additional issues to keep mind are:

When moving (upsizing) the data to SQL server, ALWAYS create a time stamp field.

When moving (upsizing) the data to SQL server, ALWAYS ensure the table has a primary key value.

If your tables have both the above, then your EXISTING forms + sub forms should work as before and should work without any changes to your code or forms. And the performance of a delete or general editing should not be ANY different of a user experiance then what you have now.

Best Regards

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

Answer 2

Hannover,

So it is worth a try. 

Edit SQL server tables IS OK but deleting and adding records is not.

I will give it a try.

Thank you!

Answer 3

When you edit the field it does update the SQL Server db right away. Depending on what's between you and your server, there may be a delay from almost instantaneous to a couple of seconds. It usually isn't an issue.

I was saying that deletes and updates through a regular Access query could take a long time when doing numerous records. I have one process that imports about 100k records. When I clear the SQL Server temp table with an Access query, it takes a few minutes but the pass-thru is almost instant.

Answer 4

Hannover Fist,

Thank you for your response.

I am familiar with PassThru, taking values from an "unbound" form and sending to SQL server tables via variables as parameters.

I agree that performance is instantaneous and I cannot sacrifice speed (waiting a "couple minutes" for updates).

To clarify,  are you saying that it is only deletes that take a long time to update or also with edits of field values.

For example:

If I a have a SQL server field from a table embedded in the form, "age", and age value = 49 and I change it to 50,  are you saying that this "edit" to that field via a direct ODBC link would NOT update instantaneously?

If it doesn't, then I can't use this method with SQL server tables.  MS Access tables will update instantaneously.

Answer 5

Yes - a linked table to SQL Server works a lot like an Access table and you can edit in forms.

You would need at least the datareader and datawriter roles for the user in SQL server.

I use a separate logon account and use the username and password rather than giving permission for individual user.

I also use a pass-thru query to run update and delete queries to let SQL Server do the work instead of using a regular Access query - it's much faster.

    strSQL = "DELETE FROM tblDailyCalls " & _

             "WHERE DateWorked < '" & Date & "'"

    CurrentDb.QueryDefs("qryPassThru").SQL = strSQL

    DoCmd.OpenQuery "qryPassThru"

This takes a couple of minutes with an Access query but a split second with a pass-thru.