Error 2105

[steve711]

Hello Everyone,

Our IT department just converted my BE to SQL and now when we go to open a form to enter NEW data we get Error 2105, Can't go to specified record.

Here is the VB code I'm using to open this form ready for entry. It worked just fine under Access but no luck now after the conversion.

I have very limited SQL knowledge, hence the problem I am sure.

DoCmd.GoToRecord acDataForm, "frm_Missions", acNewRec

Thanks for any help here.

 

[boblarson]

If you open the linked table directly you may find you cannot update it from Access. This could be because you don't have a primary key defined in the SQL Server table and/or Access doesn't know that that primary key is. Make sure to open the table from Access in design view (answering OK when it tells you that you can't make changes as it is a linked table) and see if the little key icon is there where it should be to identify the primary key.

 

[steve711]

Thanks Bob,

On the table giving me the error, no primary key. However, when I went and looked at the BE file when it is was still Access, BEFORE I gave it to IT to convert to SQL I had a primary key assigned.

Now, how do I fix this problem?

On another table it didn't have a primary key so I assigned one.

If you open the linked table directly you may find you cannot update it from Access. This could be because you don't have a primary key defined in the SQL Server table and/or Access doesn't know that that primary key is. Make sure to open the table from Access in design view (answering OK when it tells you that you can't make changes as it is a linked table) and see if the little key icon is there where it should be to identify the primary key.

 

[steve711]

Odd ball thing is that on my Access BE all but that one table had a primary key assigned.

I now looked at each of the SQL linked tables and none of them have a primary key.

 

[SQL_Hell]

How were your access tables transfered over to sql server?

If they were transfered using SSIS (or data import /export wizard) then the primary keys an indexes will not be transfered, you will need to create them manually.

 

[steve711]

I have an email into IT regarding the "how". I will assume, since all keys were lost, it was done via a wizard.

How can I add them back now that they are gone?

 

[SQL_Hell]

Go into to management studio, right click on the table and select 'design'
Then right click on the column you want to be a primary key and select 'set primary key'

Personally I wouldn't very happy about the primary keys being missed off

 

[steve711]

Thanks for the help.

I'm not very happy about this at all. The whole idea of converting was to protect our very important data, backing up, etc. The Access FE/BE was working great but I was always thinking in the back of my mind, we were one hiccup away from data corruption...

Let's see if they can get it right...