We help IT Professionals succeed at work.

Upgrading from Access to SQL 2017

SchoolTeacher54
SchoolTeacher54 asked
on
I upgraded a local Access table to a remote SQL 2017 database.
1. The connection reads data but
2. The connection won't write data.
If I use the same User and Password I can change (update) data within the SMS

I use a vb.net front end and all was working great with MS Access locally

I use a SAVE button after making some data changes and I receive an error 5 which I have not been able to resolve. I've pasted the code behind the SAVE button below.

Me.Validate()
        Me.Master_ContactsBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.HVACRedu_MasterDataSet)
        Me.Master_ContactsTableAdapter.Fill(Me.HVACRedu_MasterDataSet.Master_Contacts)
        'Master_ContactsBindingSource.Filter = "email = '" & mTempEmail & "'"
        Me.Cursor = System.Windows.Forms.Cursors.Default

Here is the connection string (READ=great, write = NO
Provider=SQLNCLI10.1;Data Source=65.175.68.37;Initial Catalog=Data_Warehouse;User ID=dw_admin;Password=***

Ideas???

Thank you,
Scott
Comment
Watch Question

Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:

I don't use the bound stuff in .NET, but I'd check the SQL that you're using to update that table. I believe it's in the Properties of the SQLAdapter you're using.

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:

If you want to be able to insert and update records in a linked SQL table, the table must have a primary key (usually an identity column, and I strongly recommend adding a TimeStamp (usually referred to as a RowVersion) field as well.


Once you have added those fields to the table in SQL Server, make sure you refresh the links to that table to get those to show up in Access.



Author

Commented:
The Access table had [email] as the PK since I need to protect against multiple accounts. Indeed the PK transferred to the SQL table and works ON the server only. I can add duplicates (which it catches) and update records there.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:

I missed the part about .NET, you might want to check Scott's response, I don't do any .Net work.


I never use anything like the email as the PK, because that can change and then you have to have referential integrity enforced to persist changes through all of the other tables that use it.  Instead, and Identity column (or autonumber) in Access is better (although there is a school of thought among database purists that says the PK should not be a surrogate, as a identity column is).


Instead, you can create a unique index on a column and use that on the email column.

ste5anSenior Developer

Commented:
Post the exact exception message.

You can use SQL Profiler to check, whether the write was sent to SQL Server and the error comes for it or is cased by your VB.NET code.

Author

Commented:
It turns out that the issue was resolved by removing the dataset and the table adapter. I saved and added everything back in and the project works well. Recall that I went from Access to SQL and it did not go well with the switcharoo.

Of note is that I later built a single field test form/project and it worked correctly. This test project did not require a provider source in the connection string whereas the "production project" required one or it would not run. JIT would stop and note that it needs one. Given that still the project would not update I took the path-of-less valor with the good news that it works.

Thank you all for the support!
Please see the note beforehand where I needed to reconstruct the data connections and, with magic prevailing the project works as expected.