Access-Dbase or remarry

[amerifax]

I have been married for 36 years. I really don't want a diorce, but I think I might have to in order to get access to work for me. It appears to get the best way to get D base to work with access it to get divorced and remarried, ha. We have tried several ways to get import data into access from a Dbase IV file. We have tried by a Dbase import and a text delimined file import. We get a subcript out of range. There seems to be several issues here. One of them is if you have numbers in a dbase text field, and try to import them intp access, the documentation and access says we have to put quotes around the numbers. That didnt work.

Actually, rather than going into great detail of what we have tried, let me just tell you that we cant import the file. Can anyone help? We have the folling type of filds in dbase: date fileds, text field with numbers, and numeric fileds. Please help.


Bob

P.S. I am happily married, so i would prefer not to go the marriage route with dbase.

 

[The_Doc_Man]

Seems to me that later versions of Access have a DBase import option. I.e. directly read a DBase database. Older versions do not. Do you recall if you had the option to include DBase translation tools when you installed Access from the Office or Access distribution disk?

You should be able to export from DBase to a .TXT or .CSV file, and if you have no embedded commas in data, you could even import that file literally. OR if you can export the DBase file in fixed format, you can IMPORT in fixed format as well.

Another part of your problem might be that Access wizards, being the dumb sh|ts that they are, mis-reads the type of a given data field. So... instead of creating a new table on import, create the table with the correct data types ahead of time and then APPEND the imported data to the empty table. This forces the data types to whatever they need to be.

P.S. I understand about marriages and merging different software. Before I met my lovely lady I was a WordStar for Windows user and loved it. We even did our wedding invitations using that package - and it worked. Wifey didn't believe it would, but admitted later that the $50 Wordstar package and $10 font disk (plus my good printer) were all we really needed. But she was a WordPerfect person at the time. The saving grace is that we both hate Word with a purple passion. It's NICE to have something in common at that level.

 

[amerifax]

Part Lucky

After a great deal of reading I was able to import 25 records, of 265,000 records, in the text format. It seems that Access needs the first 25 records to be very clean, no nulls. Also the structure has to be dead money. So I did the import of just 25, avoiding the acid test, into a new file created on the fly by Access. I will know try the dbase approach, in the morning, using your suggestion.
I was locked in with dbase real tight, with thousands of hours of dbase programming. I saw the write-ups on Office 2007 and decided to give it a try. FOR A FACT. I find 2007 to be the most major transformation of any software I have tried on the PC. There help files are the best I have used. Never could I type in a word, with MS, and come up with a close match. With 2007 I hit it every time. The only thing I hate about 2007. It was hell waiting the years. My wife who can type in word, type only, should be fling with 2007 with a few weeks of training. A little bit of a stretch but not by much. Oh will I got the time. God I love this Access

Then theirs Excel 2007. Never thought I would be able to run a pivot table on a quarter of a million records.
Oh well hopefully I will be able to import a dbf file into Access and move on with my life.

Thanks for the help

Bob

 

[Curious]

I successfully imported a whole companies dBASE III files from OpenAccess to Access 2003, using the import options from the import menu.

I ran into trouble with time conversions. If you've got any time field that needs to be imported, you'll find they import as a long integer number.

I managed to solve the problem using the formula at the bottom of the following thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=94925

My dBase files were located in OpenAccess and there is a link in the above thread to their User Forum which may help further.

 

[amerifax]

Looks Promising

I'll check it out in the mouring.

Bob
_________________________
Very carefully. Spare parts are hard to come by.

 

[amerifax]

Second Look

dBase can convert a date field, dtos(), to a character field. I was hoping that Access 2007 had a similar function that could convert the text field back to a date field with a replace. At least the logic sounds good.

Bob

 

[RoyVidar]

Convert from date to text can be done for instance with the format function

? format(date, "yyyy-mm-dd")

I think one should be extremely careful when formatting dates to text. In my view, one should only do that, if there's no possibility you want to convert it back again. I will usually only do something like that when creating dynamic SQL for the Jet engine to execute. One of the challenges, has to do with locale

12/10/2006 is 12 Oct 2006 in UK and 10 Dec in US - which provides the usual UK/US challenges
12.10.2006 is 12 Oct 2006 where I live, and makes Access choke

But, if you have a textual representation of a date, in an unambiguous format, you could investigate the CDate function, or perhaps better, the CVDate function. The latter accepts Null.

Else, the Mid function allows you to specify start position and length within a string, so lets say you have something like my date above

? Mid("12.10.2006", 1, 2) -> 12

(? is shorthand for Debug.Print in the immediate pane (ctrl+g), then just hit enter on the statement), so with a tiny bit of implicit coercion, you could do

s = "12.10.2006"
? DateSerial(mid$(s, 7), mid$(s, 4, 2), mid$(s, 1,2))

For more info on the functions, in the immediate pane, place the cursor within it, and hit F1 (though I haven't tried 2007, I think that should work there too)

Edit: Oh - you'll obviously be playing around with copies of the db to test stuff, a simple way - just enter the design view of the table, and change datatype - with a consistent unambiguous date format, that might do the trick.

 

[amerifax]

dBase to Access 2007

We will be doing the date to Text conversion in dbase. Than when we import the text data, having problems with dbase conversion to Access, into Access we will want to convert the text data back to a date field.

Bob