UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Autonumber Problem When Localising Tables, Any Versions    
 
   
johnpdmccall
post Jun 30 2020, 05:34 AM
Post#1



Posts: 1,851
Joined: 14-March 00
From: Ayrshire, Scotland


Hi Folks

I have a FE accdb connected to tables on SQL Azure.

I have localised the table (about 100 tables).

My problem is field type the fldID (Primary key on some tables) on every table is now a Number. Originally it was Autonumber until migrated to SQL Azure.
Now I need it to be an autonumber again but Access won't allow it. So far the only ways I found to fix it are:

1. Go to design view of every table and create a new fldID. Fine for tables where the fldID isn't related to any other tables
2. Create a new blank table with the structure and design I need and append the data from the original. As long as the table is structure only and has no data the fldID appends with the correct number

Is there a way of doing something like this quickly? Have I missed something in the SSMA migration process or in the localisation process?

Thanks for any help


--------------------
Cheers,
John
Go to the top of the page
 
Doug Steele
post Jun 30 2020, 08:53 AM
Post#2


UtterAccess VIP
Posts: 22,305
Joined: 8-January 07
From: St. Catharines, ON (Canada)


You should be able to rename the existing tables, create new tables with the correct names (and autoincrement fields), then insert the data from the old tables into the new ones.

You need to turn off the autoincrement feature while inserting values in the field:

CODE
Set IDENTITY_INSERT <NameOfTable> ON

Don't forget to turn it off when you're finished!

CODE
Set IDENTITY_INSERT <NameOfTable> OFF


--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
johnpdmccall
post Jun 30 2020, 01:21 PM
Post#3



Posts: 1,851
Joined: 14-March 00
From: Ayrshire, Scotland


Hi Doug,

Thanks, yes I've got a function that cleans the table names (might have been you who pointed me in that direction before)

I've created some append queries to append to new tables that have had no data entered before and that seems to work ok. So that would all have to be done by hand.


Is there any example of code (SQL?) I would need to use that would look at each old table in turn, select all fields and enter the data into the same fields in the new tables?
Something that would insert all from TableData1 into all in TableData
The old table names are suffixed with a "1" if that makes sense.


--------------------
Cheers,
John
Go to the top of the page
 
June7
post Jun 30 2020, 02:00 PM
Post#4



Posts: 1,529
Joined: 25-January 16
From: The Great Land


Perhaps you want INSERT SELECT statement.

And yes, code could loop through TableDefs collection and get table names.

Be aware, an INSERT action can populate autonumber field.


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
johnpdmccall
post Jun 30 2020, 02:06 PM
Post#5



Posts: 1,851
Joined: 14-March 00
From: Ayrshire, Scotland


Thanks June7

Have you got an example of an INSERT SELECT statement that loops through the table defs can adapt please?

My coding skills are very basic

Thanks for any help you can offer

--------------------
Cheers,
John
Go to the top of the page
 
theDBguy
post Jun 30 2020, 02:12 PM
Post#6


UA Moderator
Posts: 78,481
Joined: 19-June 07
From: SunnySandyEggo


Hi John,

QUOTE (johnpdmccall)
Is there any example of code (SQL?) I would need to use that would look at each old table in turn, select all fields and enter the data into the same fields in the new tables?

If we're talking about tables with the same structure, what happens if you simply do something like this?
CODE
INSERT INTO NewTable SELECT * FROM OldTable

Just curious...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
johnpdmccall
post Jun 30 2020, 02:25 PM
Post#7



Posts: 1,851
Joined: 14-March 00
From: Ayrshire, Scotland


Ah! Yes - I just found the same thing on docs.microfot.com and it says just what you've saidso I'll give that a try as it will be quicker than building queries in design view for all the tables. I can pop the table names in.

If there was something that would loop through table defs and do that it would be much more fun thanks.gif

--------------------
Cheers,
John
Go to the top of the page
 
theDBguy
post Jun 30 2020, 02:27 PM
Post#8


UA Moderator
Posts: 78,481
Joined: 19-June 07
From: SunnySandyEggo


QUOTE (johnpdmccall)
If there was something that would loop through table defs and do that it would be much more fun thanks.gif

I'm afraid you'll have to create that one yourself, because how could Access know which is the old table and which one is the new one?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
June7
post Jun 30 2020, 02:38 PM
Post#9



Posts: 1,529
Joined: 25-January 16
From: The Great Land


Review https://www.devhut.net/2010/06/10/ms-access...-in-a-database/

You could change If Then

If td.Name Like "*Old" Then

Or whatever you used to identify old tables when you named them.

Within loop, execute action query. Something like

Set db = CurrentDb
For each td ...
If ...
db.Execute "INSERT INTO [ & Left(td.Name, Len(td.Name)-3) & "] SELECT * FROM [" & td.Name & "]"

This post has been edited by June7: Jun 30 2020, 02:47 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
johnpdmccall
post Jun 30 2020, 02:40 PM
Post#10



Posts: 1,851
Joined: 14-March 00
From: Ayrshire, Scotland


Of course
QUOTE
how could Access know which is the old table and which one is the new one?


It would probably take me longer to try to work that one out than just to write the previous stuff for each table.
laugh.gif

Thanks everyone!

--------------------
Cheers,
John
Go to the top of the page
 
theDBguy
post Jun 30 2020, 02:46 PM
Post#11


UA Moderator
Posts: 78,481
Joined: 19-June 07
From: SunnySandyEggo


Not necessarily... All you need to create is a mapping table, so Access can tell which one is the old and which one is the new table.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
June7
post Jun 30 2020, 03:29 PM
Post#12



Posts: 1,529
Joined: 25-January 16
From: The Great Land


John,
Did you see post 9?

This post has been edited by June7: Jun 30 2020, 03:30 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th July 2020 - 02:09 AM