Import MDB file to other database? 2

[jlockley]

This comes up every now and then, but then you are all getting smarter. IF Paradox is on its way to obsolescence (I think that's a word) then at some point I have a lot of databases with .mdb files to get into another format. I'd look at Filemaker, but Access would be fine. Has anyone figured out a way to do this yet?

 

[Kliot]

You've got me puzzled .mdb files are Access files. If you want to switch to Access from Paradox simply import the Paradox files into Access. Paradox doesn't export to Access but Access imports Paradox.

 

[jlockley]

Of course I have you puzzled, because I am way off base. It's the MB files that won't go in, or do you know a trick? I remember when my brain worked after 5:00. Sorry.

 

[TonyMcGuire]

Your option is to open the files with a program whose ODBC driver can access the memo portion. And I think M$ only supports to Table Level 5.

Or roll your own export using textstream.

If you need a sampler of rolling your own, let me know.

Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.

 

[Kliot]

I'm still puzzled, a mb file is a file that is associated with a db file and it contains large object such as memo fields, blobs, graphics etc. This file should be imported automatically when you import the db file, I can't vouch for all file formats but I know that Access will have no problem importing memo fields. Is there a particular field type that you are having trouble importing?

If all else fails you can always do as Tony suggested and write your own export program to extract the data.

 

[jlockley]

I'd love the example. In the meantime I will fool around to see what access is capable of doing.
Thanks much.

 

[TonyMcGuire]

The below uses tab to separate fields ("\t"). Replace with another character if you prefer.

And, of course, with memo field data you may need to choose a 'special' character to make it work, depending on the limitations of the program you will import the data into.

var
tc   tcursor
ts   textstream
i,
li   longint
endvar

ts.open("export.txt","nw")
tc.open(":alias:tablename.db")
li=tc.nfields()
scan tc :
  ts.writestring(string(tc.(1)))
  for i from 2 to li
    ts.writestring("\t"+string(tc.(i)))
  endfor
endscan
tc.close()
ts.close()

Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.

 

[jlockley]

And you think this will work for fields with word documents? It's going to be a learning curve, but I will try it. JLL

 

[TonyMcGuire]

NO! It will NOT work with binary stuff.

I thought you just had memo fields (text) in there.

For binaries, you need to write them out to an exterior file (reconstruct them as Word docs, for instance).

That's binary - do you also have OLE? Whole nuther story there to extract them.

Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.

 

[jlockley]

I am afraid yes. (OLE). By write the out, you mean open and transfer one by one, right?

 

[Kliot]

Now we're getting somewhere, you have binary data. I just tested Access and it doesn't want to import binary data.

You can do something like this, this will create word files from the binary data, you can then import them back into whatever database you choose.


var
binVar Binary
fs FileSystem
tc TCursor
endVar

if tc.open("table.db") then
scan tc for not isBlank(tc.binaryField) :
binVar = tc.binaryField
binVar.writeToFile(tc.fileName + ".doc")
endScan
endif

 

[jlockley]

Now that is awesome. I have wanted to do that forever. (export all of the documents in a sorted set to word files, that is). This imports them, however, as individual files, or not?

Let's say I have a database of some 400 (really much more), some of which have binary files, some of which don't. Is it possible for me then to import the whole batch of them into Access in the same order as I exported them without dealing with them individually?

thanks. JLL

 

[Kliot]

When you import the Paradox database into Access, Access imports all the records it just drops the binary field. The sort order will be the sort order of the original database, that is I think, I doubt that Access takes into account secondary indexes. This really shouldn't be a problem just re-sort the data the way you want in Access.

The code I gave you will just fill a directory with individual word files sorted by file name, you will have to decide how to name the files, if you have a unique field like Employee Id, you can use that id as the file name. Just name the file (tc."employee id"+ "doc"}.

Ultimately the thing to keep in mind is that Paradox is very powerful and flexible and you don't need to worry about loosing data because of it's obsolescence.

 

[jlockley]

I am dying to try it, but I am swamped. I will try to get it done next week and let you know. Thanks so much. JLL

 

[Kliot]

Let us know how it works and as usual if you get stuck we'll walk you through it.

Perrin

 

[jlockley]

aha. You know I usually get stuck. JLL

 

[jlockley]

Question: Not all records have binary information. Will this cause problems?