Data transfer between tables with auto numbering PKI.

[JGT]

Hi, back again with one more trouble.

While there is no problem with data transfer between 2 Access tables with auto numbering PKI - Access keeps the key values equal in the 2 tables - when transferring from Access to Sql Server, the latter generates its own values in the PKI column, so you no longer can compare the two tables, nor is it possible to transfer data to related tables – as the key value needed for the joined fields changed.

Curiously you can change the values under the key in the SQL tables – something not possible for Access auto number PKI - so you could correct the SQL PKI column after transfer. However to do this you would need a reference to identify the registers, ie add a clone column to be filled with the original PKI data.

Or you don´t use SQL auto number PKI before transfer, changing the SQL PKI to auto numbering after data transfer (I suppose you can do that)

I don’t like any of the 2 solutions. Who knows a better way out?

Thank you for any reply,

 

[SQL_Hell]

I have never seen or heard of this happening before, I think you may have something wrong, do you have identity insert turned on in SSIS? Is the column you are inserting into already an identity field?

Anyway you could up load a copy of the database with some data in that I can try and upload to sql server to see if I get the same results?

 

[JGT]

Hi Hell, thanks once again for being interested in my problem(s).

Well unfortunately it looks like that I am a good producer of things that never happened before, this is the second time since I started using SQL a MVP states such a thing.

Not an SQL Server expert at all, I just want to change an Access back-end to SQL BE, what according to what one can read about, should be easy; as easy as the fact MS calling it an upgrade.
Because testing on the corporate network is quite difficult as the SQL Server is a corporate issue with restricted access, I installed the SQL Server Express 2005 at home. May be this could cause some problems, as corporate SW is still based on XP 2003 so I am using Access 2003/SQL 2005.
What I did next was simply running the Access Tool Upsizing Assistant who generates the equivalent SQL BE, I am trying to use. As a matter to be sure that things are all OK, I wrote a routine to erase the SQL Table content, and another one to upload them again. ( I can use this later on when I put the SQL BE in production). A check routine compares the content of the tables (in terms of qty of registers). And what works fine with Access, doesn’t work with SQL. At a first glance primary tables are OK, but the next tables stay without registers. Examining primary tables with autonbr PKI however shows that they are not a copy the Access PKs. Therefore subsequent registers are all rejected.
As I do not have SQL Server experience, I thought this was a typical difference between dbs. You saying “I have never seen or heard of this happening before”, I conclude it is not.

You ask if identity insert is turned on in SSIS? – Unfortunately I must show here my beginner face as I do not know how to check this. I installed SSMSExpress and looked for identity insert. The only info regarding Identity I got (opening Tables/Columns/Id (PK,int,not null) ) is: Identity false / Identity seed 0/ Identity increment 0.

Is the column you are inserting into already an identity field?
Before, only 2 of the tables gave me the #3622 error because of the identity column, so they surely should have, but now all 12 autonbr PKI tables show me the same info above: false/0/0. I do not understand this.

I will build a new small db with some trouble tables and some content, and process it with the Access upgrade assistant. If testing shows the same result I can send you the .mdf /.ldf files, and the .mdb.

 

[JGT]

Hi Hell,

The new small Access db with auto nbr random PK tables I built went thru the Access build in Upgrade Wizard, generating the SQL db.
Executing an append-query transferring the data from the Access tables to the linked the SQL tables, the PK column data changes, confirming the former experience.

Annexed a 1 table Access db with equivalent SQL files.

In the mean time I put an extra clone-PK column for all auto nbr random PK Tables. Uploading a table, this column gives me the possibility to correct the SQL PK column, restoring the original Access PKs. This way the data transfer from Access to SQL proceeds without any loss of registers. Ugly solution, but a way out in case nothing else shows up.

 

[SQL_Hell]

Hi there

Sorry I am very busy at the moment, if i get time I will have a look at your attachments this afternoon.

 

[JGT]

Hi Hell,
Looking at the amount of your precious time you spend on helping other people with their problems you surely don’t need to apologize because you are busy.

 

[SQL_Hell]

Hi there

Just done some tests with this and I managed to copy over the tables to sql server without any problems and the data looks good.

Here's what I did.

1. Attached the data files to sql server (obviously you wont need to do this)
2. Dont bother upsizing, do this instead.
3. open the database in sql server management studio.
4. delete your existing 'tasks' table in sql server, or rename it
5. right click on the database name, select 'all tasks' , then select 'import data'
6. a wizard loads up, select the data source as access database and bowse to the file location (need to have access closed for this), press next.
7. set the destination (should already be set by default), press next.
8. select copy data from tables. press next
9. select the table you want by clicking the box and set the destination table, in your case the source table is 'tasks' and the destination is also 'tasks'.
10. Make sure execute immediately is selected and press next.
11. the data will now be imported.

Now have a look at your table and check the data against whats in access.
It should be fine.

After you are happy with the data, then right click on the table in management studio and select design, make field 'Idtask' a primary key and change it's data type to 'bigint', then select identity = yes, close the table and say yes to saving.

I do find it strange that your data is auto-number in access yet it has minus values??

 

[datAdrenaline]

If you plan to use the resultant table as a source to an Access front end via a linked table, then I want to toss out the fact that a BigInt datatype gets traslated as a TEXT value, which can have very unexpected results if you are unaware of that fact. I personally would suggest that you keep the datatype in SQL Server for your Identity columns as INT (4 byte integer), simply because Access/ODBC can directly translate that value and type.

 

[SQL_Hell]

The reason I chose bigint is because the data was close to the threshhold for INT, but I didn't know about this text / bigint problem.

Another good reason to use access project over .mdb

 

[datAdrenaline]

>> The reason I chose bigint is because the data was close to the threshhold for INT <<

The values of the D/L do indicate that, however, it is my opinion that the OP CHANGE the way the values are granted by the Access. You see in JET on can choose to have the autoid numbers distributed with "Random" (thus the negative numbers you inquired about) which is how this Access table is set up .... Possibly set as Random as a result from replication. However, with SQL Server, I beleive (please correct me if I am wrong) that you can only increment.

Also, in Access, if you are set up to increment, you can go up to the high limit of a Long (2147483647), then on the NEXT record, the autoid assigned will be (-2147483648) and start marching towards 0 ... I beleive SQL Server will error out on the next record after 2147483647 has been issued, but I would imagine you would be able to programatically reset the seed and start incrementing through the negative set of values that SQL Servers INT (Access/JET's Long)

>> Another good reason to use access project over .mdb <<

While I agree that an ADP is an Excellent tool, and I use them daily to modify the Schema of my SS2000 databases, I am not convinced the flexibility of that format can top an MDB/ACCDB! .. Also, the absence of an increased feature set in A2007 for an ADP kinda makes me think cautiously about a dynamic future for ADP's ... but that is just an opinion. I do wish MS would further the technology though as I find them to be an excellent choice for me simply because my IS dept. will not allow Enterprize Manager (or any other SQL Server managent tool) on my PC, so I use ADP's quite successfully ... I just can't administer security as easily!

 

[SQL_Hell]

However, with SQL Server, I beleive (please correct me if I am wrong) that you can only increment.

Yes that's correct, you can only increment.

Also, in Access, if you are set up to increment, you can go up to the high limit of a Long (2147483647), then on the NEXT record, the autoid assigned will be (-2147483648) and start marching towards 0 ... I beleive SQL Server will error out on the next record after 2147483647 has been issued, but I would imagine you would be able to programatically reset the seed and start incrementing through the negative set of values that SQL Servers INT (Access/JET's Long)

Yes I agree, actually I am not sure you can reset the seed programmically for an identity column, the only was to re-set that I know of is to truncate the table.

While I agree that an ADP is an Excellent tool, and I use them daily to modify the Schema of my SS2000 databases, I am not convinced the flexibility of that format can top an MDB/ACCDB! .. Also, the absence of an increased feature set in A2007 for an ADP kinda makes me think cautiously about a dynamic future for ADP's ... but that is just an opinion. I do wish MS would further the technology though as I find them to be an excellent choice for me simply because my IS dept. will not allow Enterprize Manager (or any other SQL Server managent tool) on my PC, so I use ADP's quite successfully ... I just can't administer security as easily!

Sorry I was just being flippant, trying to bring up that age old argument

 

[JGT]

Hi Hell,

Thanks for taking the time to try this out. I can use yr method at home but unfortunately at work I have the same problem as datAdrenaline, the only tool CIO allows to use is the Analyzer (SQL Server 2000), even so only on the test SQL Server environment . When we go over into production, we will be 100% CIO dependent.
Still a strange thing you can push but not pull the data from Access to SQL.

First time I looked at negative PKs, I also found them very bizarre; but datAdrenaline is right when he tracks the use of random auto number PK back to replication. The original db is a replicated one indeed. I shall not try to replicate the SQL db though, too much trouble working with replated db; but independently today I like and prefer to use random PK.
However maybe the use of this kind of PK results in SQL generating its own numbers, not accepting the original ones? I will try this out.

Your comments regarding adp surprised me. As far as I am able to conclude from what I read, it is not cautious to develop new apps with adp. Is this not so?
Like with (Access) replication, there are always professionals liking things so much they simply won’t accept extinction. So going through forums you see the discussions belonging to two groups, the defenders and the opponents. The first insist on the exceptional qualities, while the latter, not being necessarily against, give you the alert that the manufacturer stopped further development so you to better keep out. The problem is that, to avoid protests, there is no clear manufacturer statement, only rumors about the supposed future of that particular application.

Thank you once more

 

[JGT]

Well, thats it, mystery cleared, SQL doesnt except the PK because they are random generated numbers. I cleared the orig. PK column, setting up a new one, using incremental mode. No more problems to upload the data from Access into SQL.

 

[dfenton]

There is a clear Microsoft statement about the direction of development in Access for use with SQL Server: MDB + ODBC is the preferred method, while ADP + ADO is deprecated. If you want the URL, I'll have to look it up.

 

[Banana]

Dfenton,

I would like to see that statement myself (even though I didn't ask that question)- I tried to google for that but didn't find that.

Another reason why that piqued my curiosity was because I had understood that 2007 had native client, so there is no need for ODBC with SQL server, no?

 

[JGT]

Hi Dfenton,

If it is not too much work to look it up, I think a lot of people could be interested in seeing this MS statement.

 

[dfenton]

Here's the link:

http://technet.microsoft.com/en-us/library/cc178973.aspx

The heading for the specific comments deprecating ADPs is "ccess Data Projects (ADPs)".

 

[datAdrenaline]

I love ADP's for changing schema ... so I am not bashing ADP's ... but I wanted to hilite a sentence or two from the article David linked us to {thanks David ... thats a great link to save as a Favorite!}:

Heading: Access 2007 and SQL Server
>> Access forms and reports can be optimized as efficiently as Visual Basic front-end for SQL Server. <<

>> The preferred way to connect to SQL Server is MDB file format or ACCDB file format. This enables you to use the full flexibility of local tables and local queries, while leveraging the full power of SQL Server. <<

Heading: Access Data Projects (ADP's)
>> Because of the layers required to get from Access to SQL Server in the ADP architecture, it is often easier to optimize MDB/ACCDB file solutions. <<

....

In addition, I know that I personally use to think Deprecation meant Deleted or Removed. But is basically means something is considered obsolete (or not preffered) but still available for use
http://en.wikipedia.org/wiki/Deprecated