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
> Migrating Tables From 2002 To 2010, Any Version    
 
   
azizrasul
post Oct 25 2019, 06:08 AM
Post#1



Posts: 1,514
Joined: 18-July 00
From: Faisalabad, Pakistan


I have a series of old MS Access 2002 mdb databases which need to be converted to MS Access 2010 before ultimately being converted to MS Access 2016.
To cut a long story short, I keep getting a dialog box telling me that I need permission despite the fact I have been given the username and password on the MS Access 2002 db.

So to overcome this problem, I have decided that I can just as "easily" create the MS Access 2010 accdb db by recreating the objects.

I am trying to obtain the necessary information to obtain all the design data I need for the MS Access 2002 tables so that I can then use that data to recreate the tables in MS Access 2010. I have written the following snippet of code in the MS Access 2002 mdb db to get the name of the table and some of it's fields properties:-

CODE
            With rst2
                For i = 0 To rst3.Fields.Count - 1
                    .AddNew
                    !TableName = rst1!tablequeryname
                    !tablefieldname = rst3.Fields(i).Name
                    !DefaultValue = rst3.Fields(i).DefaultValue
                    !FieldSize = rst3.Fields(i).FieldSize
                    !OrdinalPosition = rst3.Fields(i).OrdinalPosition
                    !Required = rst3.Fields(i).Required
                    !Size = rst3.Fields(i).Size
                    !Type = rst3.Fields(i).Type
                    !ValidationRule = rst3.Fields(i).ValidationRule
                    !ValidationText = rst3.Fields(i).ValidationText
                    .Update
                Next i
            End With

I have two questions, is there any other table or field information I need to obtain to recreate the tables in MS Access 2010 db?
Secondly, what would the code look like using the results above to recreate the tables in MS Access 2010?

--------------------
Aziz
Go to the top of the page
 
Jeff B.
post Oct 25 2019, 07:37 AM
Post#2


UtterAccess VIP
Posts: 10,336
Joined: 30-April 10
From: Pacific NorthWet


Have you already tried opening a new empty Access db in 2010 (or even 2016) and importing the tables from the 2002 version?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
azizrasul
post Oct 25 2019, 07:44 AM
Post#3



Posts: 1,514
Joined: 18-July 00
From: Faisalabad, Pakistan


Yes, no luck. Spent a lot of time trying to resolve it hence the new approach.

Also considering whether it would be just as easy to export the table data into a spreadsheet and importing into the 2010 db and see if that gives me what I want.
This post has been edited by azizrasul: Oct 25 2019, 07:46 AM

--------------------
Aziz
Go to the top of the page
 
GroverParkGeorge
post Oct 25 2019, 08:01 AM
Post#4


UA Admin
Posts: 36,181
Joined: 20-June 02
From: Newcastle, WA


That probably means the 2002 mdb(s) have User Level Security enabled, and that would mean you need the .mdw files for each one of them. Can you find those files? They would parallel the mdbs. i.e. DatabaseOne.mdb would have DatabaseOne.mdw associated with it.


--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
azizrasul
post Oct 25 2019, 09:37 AM
Post#5



Posts: 1,514
Joined: 18-July 00
From: Faisalabad, Pakistan


I have the System.mdw file. I can open the 2002 file OK, but when I try to drag and drop into the 2010 db, it doesn't work. Also can't see the 2002 file when I open the 2010 file. Can't export from 2002 to 2010, etc. etc.

--------------------
Aziz
Go to the top of the page
 
GroverParkGeorge
post Oct 25 2019, 10:00 AM
Post#6


UA Admin
Posts: 36,181
Joined: 20-June 02
From: Newcastle, WA


"...when I try to drag and drop into the 2010 db..."

I wouldn't try this with Drag and Drop because of the differences between the mdb and accdb formats. I'm sure exporting from an mdb to an accdb wouldn't work because the older version would have no way to identify and work with the newer. I am a bit surprised that you can't even see the 2002 mdb, though.

I no longer have any 2002 format mdbs to test on. Can you provide one for review?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
azizrasul
post Oct 25 2019, 10:10 AM
Post#7



Posts: 1,514
Joined: 18-July 00
From: Faisalabad, Pakistan


There you go. No mdw file.
This post has been edited by azizrasul: Oct 25 2019, 10:11 AM
Attached File(s)
Attached File  MS_Access_2002_Database.zip ( 7.1K )Number of downloads: 5
 

--------------------
Aziz
Go to the top of the page
 
GroverParkGeorge
post Oct 25 2019, 10:31 AM
Post#8


UA Admin
Posts: 36,181
Joined: 20-June 02
From: Newcastle, WA


Hm. What I was hoping for was one that you're having trouble with....

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
azizrasul
post Oct 25 2019, 10:45 AM
Post#9



Posts: 1,514
Joined: 18-July 00
From: Faisalabad, Pakistan


Sorry, let me send that to you on Monday.

--------------------
Aziz
Go to the top of the page
 
Jeff B.
post Oct 25 2019, 01:07 PM
Post#10


UtterAccess VIP
Posts: 10,336
Joined: 30-April 10
From: Pacific NorthWet


"Yes, no luck."

Not sure what you tried from that description.

Have you opened a (new, empty) Access db in either 2010 or 2016 version, then attempted to import the tables from your 2002 version db? If so, what happens?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
azizrasul
post Oct 25 2019, 05:44 PM
Post#11



Posts: 1,514
Joined: 18-July 00
From: Faisalabad, Pakistan


I did try that. Plz c opening post.

--------------------
Aziz
Go to the top of the page
 
dmhzx
post Oct 26 2019, 02:53 AM
Post#12



Posts: 7,115
Joined: 22-December 10
From: England


Not sure if you've tried this one.
Create a new empty database with Access 2010 (ot if you have it 2016) and save it as an MDB
Open the 2002 datbase and export all the tables to your new MDB
Save the new MDB as an accdb
This post has been edited by dmhzx: Oct 26 2019, 02:55 AM
Go to the top of the page
 
Jeff B.
post Oct 26 2019, 08:13 AM
Post#13


UtterAccess VIP
Posts: 10,336
Joined: 30-April 10
From: Pacific NorthWet


<azizrasul>

Sorry, I must have mis-interpreted "recreate"...

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
PatHartman
post Nov 3 2019, 03:57 PM
Post#14



Posts: 71
Joined: 20-February 03
From: Stratford,Ct USA


If your .mdb database is secured, you will need to remove the security before you do the conversion. I'm including an old article on ULS that should help you to remove security. SECFAQ.doc is the file with the details. The others are additional information.
This post has been edited by PatHartman: Nov 3 2019, 03:58 PM
Attached File(s)
Attached File  SECFAQ.doc ( 241.5K )Number of downloads: 6
Attached File  SecurityOverview.doc ( 337K )Number of downloads: 1
Attached File  Security.zip ( 42.11K )Number of downloads: 3
 
Go to the top of the page
 
azizrasul
post Nov 4 2019, 05:01 AM
Post#15



Posts: 1,514
Joined: 18-July 00
From: Faisalabad, Pakistan


Thanks Pat.

--------------------
Aziz
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2019 - 02:57 AM