Home » MS OfficeRSS

Looking to convert Access 1.0 MDB databases to later version

Hello.

We have several Access 1.0 MDB database files that were created using Microsoft VB 3.0 programs.

Is there an easy way to convert these database to the latest version of SQL?  We would like to use the converted files in at least a VGB 6.0 program and possibly in .NET VB programs.

We use a program called DATAMGR.EXE to open and maintain these MDB files.

Any help would be gratefully appreciated.

Thanks,
Tony

 

15 Answers Found

 

Answer 1

If by access  1.0 MDB database  you mean a database that was created  with the original version  of Access released in 1992, pardon me, 18 years ago. You are mostly likely outside the window of the most recent versions of Access being able to open  older versions. MS maintains a certain level of backward compatibility but isn't going to support a file format that was created 18 years, pardon me, 2 decades ago.

Unless there's an app out there that can do the trick (which while possible isn't likely given the limited market), you'll most likely have to recreate the tables manually if you're able to view them in Design mode at all. Toss in there that the earlier versions of Access namely 1.0 and 2.0 had very limited export functionality and nothing when it comes to VBA. If you want access to the data, you may have to rekey all of it. The headaches that you will most likely face in upsizing is the actual business case for organizations to remain somewhat current as later versions of an application are released.

You're best bet is to start from scratch.

Would you continue to use a computer with a Intel 286 processor running at 25 MHz in today's world of high-speed internet?

 

Answer 2

Hello David.

Thanks for your reply.

Tony

 

Answer 3

Keep in mind that accesss 97 to 2002 can import from an access  2.0.

 

Access 2003 can import 2.0 if you download + install converter.

 

So, are you sure this is a 1.0 access file?

 

Access 2.0 was out in 1994, and access 1.0 in 1992.

 

Also, try excel, it always amazed me at how it could import old dbase and all kinds of older formats that I can't even remember that existed.

 

You could certainly try access 97 or even access 2002 to see if the data can be imported.

 

Albert D. Kallal  (Access MVP)

Edmonton, Alberta Canada

 

 

 

Answer 4

If it's a matter of importing the data (rather than recreating the functionality of the application), it's doable. I actually still have a (legal!) copy of Access 2.0, and I've used it to "bootstrap" from 1.0 to 2.0 to 97 to pretty much whatever.

So... do you want to convert  the program - the user interface? If so, I agree with the others who say you're much better off to start from scratch; there are much better and easier to use tools available now. Or do you just want to salvage the data?
 

Answer 5

OFF TOPIC: I miss the 2.0 icons.
 

Answer 6

David, some of your statements are incorrect. The OP does NOT have to rekey the data. There is an upgrade path from 1.0 to 2.0 to 2002 to 2010. Not pretty, but not rekeying either. If the OP contacts a long-term Microsoft Solution Provider like the company I work for, they may still have all these versions in their MSDN CDs and can upgrade the data.

The UI and business logic would have to be rewritten.


-Tom. Microsoft Access MVP
 

Answer 7

You might want to try useing OpenOffice Base.  It should be able to import the database  and then maybe save it as an MSOffice 2000 file.

Take a look at http://searchenterpriselinux.techtarget.com/tip/OpenOffice-Base-Converting-data-and-files-from-Access

Best of luck,

Joe

 

Answer 8

That will not help, the issue + problem here is that any product (vb.net, VB6, FoxPro, FileMaker, c++, c# and Base as you mention) ALL USE the default set of ODBC drivers and version  of JET that has shipped with windows for 10+ years for importing.

In point for 10+ years you NEVER needed to install access  on a computer to import and pull data out of access database  files. You can even use windows scriting without anything else having been installed to pull data out and read those access data files. Again, you could always do this without having access installed.

 

Bottom line:

 

Without something that supports this 1.0 format, you not going to use a later version of access (or the odbc "jet" drivers that all products rely on in windows). In other words, what your suggesting will not help  since it relies on those later drivers.

 

So, the issue here is just that 1.0 is so very old. As a few mentioned here, you have to go from 1.0 to 2.0. At that point, then access 97 to 2002 (by default) can import.

For 2003-2007, installing 2.0 file support as a download will extend this to access 2003 and 2007. I am not sure if there is a add-in for access 2010 right now, but considering that 2.0 is 1994 format, I can't say this is real issue.

 

The bottom line here is get the data into 2.0 (1994) format, and then you quite easy  be able to find someone with most recent editions of office to import the data to a later format.

And, perhaps this is not a 1.0 format, and is in fact 2.0. Testing a import with access 97 to 2002 would verify this quite easy.

 

Albert D. Kallal  (Access MVP)

Edmonton, Alberta Canada

 

Answer 9

If you check, you'll see that I stated 'may'. I did given that I'm well aware that while the probability of a solution being out there is slim, its not outside of the realm of possibility. Case in point, the possibility that Excel could pull the data out. Granted, I hadn't considered the possibility that some firms out there might still have their 2.0 disks laying around.
 

Answer 10

I want to thank everybody for their ideas and suggestions.

I'm pretty sure these MDB files  are 1.0/1.1 files.  I don't think they were created  using Access.  They were created using a VB 3.0 program.

How do i tell for sure?  Test an import using access  2002?  I can open  and access these files using ADO in VB 6.0.  Is there some method or property I can use to determine the format?

We are only interested in converting the database  to a later version  so that we can use it with more recent programming technology.

I am in the process of analyzing the suggestions i received and trying to install the software needed to attempt a conversion.

Thanks,
Tony

 

Answer 11

That sounds like very encouraging news indeed. In fact if you can open  this with ado and a standard jet connection OLDB 4.0 string, then this suggests that you at least at 2.0 format or even perhaps later.

 

So yes, just fire up a recent edition of access. Create a new blank database.

 

Then go file->get external data, and import.  You can import all of the data and tables in "one step" and the result will be a database  in the current format. Note that table relationships , etc. will remain intact when you do it this way.

 

Judging by what you said and if you can use an ado connection string, then it's in a later format anyway.

 

In fact if you have any version  of access  running on your machine now, I would try opening the file directly and see what kind of message you get.  (always work on a copy if you going to DIRECT open the file with access since access will ask of you want to convert  to a later format. Often don't want to do this on your only copy of a file and when you are in a hurry you can often ignore or mess those prompts.

 

It is best to do the file->external data. But, it access converts it direct, that is ok.

 

You don't mention what kind of ado string you're using with VB6, but if you are using a JET OLDB 4.0,  then JET data engine jet is opening this file. Remember, your ADO does in fact use the JET engine here. And, point here is that Access also uses that SAME JET engine.

 

In fact you have access on a machine, I would try opening a copy of a file and see what happens.

As for an easy  way to determine exactly what format it is, I don't have a good answer for you. However if you do the file get external data tip above, you will be using a later edition of the JET file format. This would allow you to use the tools in access to migrate the data and upsize it to SQL server if you so desire.

Albert D. Kallal  (Access MVP)

Edmonton, Alberta Canada

 

Answer 12

Hello Albert.

Thanks for your help.

I only have access  2007.  I tried using that, but i could not find external data/import, so I just tried opening it.  It gave me a warning "This database  was created  using a previous version  of Microsoft Office Access."  If i click YES to  convert  it, it gives the message "This operation is not supported for Microsoft Office Access 1.x databases."

So, it seems like it knows that the mdb  file is version 1.0/1.1.

I also managed to read through the MDB file using ADO.NET in VS 2008 VB.  The code is used is here:

Dim coyneEDMSCtlConn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Dataflow\DB\edmsctl.mdb")
  Dim coyneEDMSCtlCommand As OleDbCommand = coyneEDMSCtlConn.CreateCommand()
  coyneEDMSCtlCommand.CommandText = "SELECT * FROM Control"

  coyneEDMSCtlConn.Open()

  Dim coyneEDMSCtlReader As OleDbDataReader = coyneEDMSCtlCommand.ExecuteReader()

  If coyneEDMSCtlReader.HasRows ThenDim XXX AsStringDim bbb AsBooleanDoWhile coyneEDMSCtlReader.Read()
    Console.WriteLine(vbTab & "{0}", coyneEDMSCtlReader.GetBoolean(1).ToString)
    bbb = coyneEDMSCtlReader.GetBoolean(1)
    XXX = coyneEDMSCtlReader.GetName(1).ToString
   LoopEndIf

  coyneEDMSCtlReader.Close()
  coyneEDMSCtlConn.Close()

I think i will try to convert the files  using a VB program.  Does that make sense?

Thanks for all of your help.

Tony


Stop The World, I want To Get Off!
 

Answer 13

Your idea of using a VB program  is possible, but it certainly doesn't make sense if a simple import option would will work for you. So you should try the external data suggestion because it only takes a few seconds of your time and the upside of that benefit would be that you don't need any code to be written at all.

 

So just create a blank database  in access  2007, then try import.

 

The external data tab can be found here:

 

 

Keep in mind that the external data tab is ONLY available and active when you have an existing database currently opened (of which you plan to import data into). So, you must create a blank database before you attempt a import.

 

So click on the access icon in the import and you get a option to link, and an option to import (you should/will eventually try BOTH options if one of the other doesn't work). You first choice to try is import. I'm not 100% sure that importing will give you better luck than attempting to convert, but usually it's a little bit more flexible and gives you a little bit more leeway in terms of the file formats. It worth a try for the time it takes.

 

When the wizard launches or importing data, choose the option to import data, and then simply browse to data file. If that works you are home free and all of your data can be pulled into a newer format.

 

If importing does not work, then I would attempt to see if linking works, and if that works then once again you might have to write a little bit code and use a few append queries, but again it's gonna be far less work than writing your own import routines from scratch.

 

And in fact if linking to these tables does not work (if after importing don't work), I would hunt around and find an older version  of office like access 97 and simply import this. I just don't think it's a great idea to resort to writing a whole bunch of code until the other "easy" possibilities are first exhausted.  If you don't have any of these other choices, then certainly coding is another choice in your list of possible approaches here.

 

Albert D. Kallal  (Access MVP)

Edmonton, Alberta Canada

 

Answer 14

Wow!!! That was easy.  It took exactly three seconds to import the whole database  with 22,000 history records.

It looks like i will not need to write a program  to do this.  Thanks for pointing that out for me.

Should i leave the imported Access Database(.accdb) as is, or can i export it to an SQL database file.

Do you still feel the original file i have is not from Access 1.0/1.1?

Thanks again for all of your help.

Tony

 

Answer 15

Should i leave the imported access  Database(.accdb) as is, or can i export it to an SQL database  file.

You can well leave it in current format. Access can connect to a lot of different systems and you can even send the data up to SharePoint if you want. So, you have the data in a great veggie matic data slicing system now.

 

If you have sql  server sitting around handy, then you can use the built-in UPSIZING tools in access and it will just send the data right up to sql server. Do test this on a copy if you new to using this feature.

 

Note that you can upsize just the one table at a time, or send the whole set of tables up to sql server. Just click on the database tools tab on the ribbon and you will see a SQL server button in the "move data" group on the ribbon. That option can be used to send data up to sql server.  

Do you still feel the original file i have is not from Access 1.0/1.1? 

Hum, I think so. You did get an error message suggesting as such (so, it 1 or 2). However, I did not think that access 2007 without anything was going to be able to convert  a 1.0 (so, I can't be 100% sure). However, I am quite impressed that it still does import a data file from 1992 (and I did not think even importing would work on 1.0 files, so that why I think this is 2.0)

 

In fact, often people don't care about a conversion, but a import as you done is fine for getting the data out. So, in a sense if someone asked can you convert, we might have said "no, you can not", however, at the end of the day, sometimes small semantics on what is being asked can make a huge difference. It not every day we get a question like this here, so I am quite impressed.

 

Anyway, your much on you way regardless where that data eventually goes.

 

Albert D. Kallal  (Access MVP)

Edmonton, Alberta Canada

 

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter