OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Problems creating database from MS Access

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
DVB
Power User
Power User


Joined: 01 Feb 2008
Posts: 99

PostPosted: Tue Feb 03, 2009 7:31 am    Post subject: Problems creating database from MS Access Reply with quote

I have a database and spreadsheet of responses to an online questionnaire, part of my PhD research. They were set up for me using MS s/w. I can open the Excel spreadsheet with no problem using OOo Calc, but the database is proving more problematic. I've read the stickies here and I've followed the most straightforward instructions I could find, at http://salahuddin66.blogspot.com/2007/09/mdb-file-in-openofficeorg.html -- basically, open OOo Base, select Connect to an existing database, select Microsoft Access, browse for the .mdb file, follow the steps and register and save the .odb file.

Yesterday I was able to do this, and play around for a while with Queries, Forms and Reports, looking at and trying to output-format three fields. (It's many years since I've worked directly with databases, so a. I'm having to learn-as-I-go and b. I'm inevitably making lots of idiot mistakes.)

Two problems yesterday, before today's big one. First, after looking at these three fields I tried looking at some of the others -- and found that the other 120-odd fields had vanished, leaving only the three I'd initially looked at. It seems I'd saved the three selected fields as the entire database. Obvious solution: delete and start afresh -- except (problem 2) when I tried to delete the .odb file I was told I couldn't because it was in use with another program (it wasn't). Eventually I gave up, turned the machine off and went to bed.

Today I deleted the offending .odb file and created a fresh one from scratch. Problem: there are no fields listed at all. The original .mdb file is 1.07 Mb. The new .odb file created from it is 2.2 Kb -- i.e. empty.

I've clearly done something stupid, but have no idea what. Can anyone help?

David
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Tue Feb 03, 2009 10:13 am    Post subject: Reply with quote

Hello David

I understand that you can open the Microsoft Access with the .mdb extension.
You read and change the table.


You saved the OOo_base database.
You see it was small. That is correct there you only save some forms and/or querys in OOo-base and not the table. So no problem.If you have add or change some things that is saved in Microsoft Access. I understand your post in the way that you did not import Mircrosoft Access.

ReMove Filter to see all records
You see only some records and not the complete table.
I'm fairly sure that there is nothing happend. Possible the filter is still activ and you see only what passed the filter.

To see every thing remove the filter. There is button active if there is a filter active. The button is on the top-line or bottom line. The filter setting are saved.


I hope this helps you and that you have all the records.
Romke
Back to top
View user's profile Send private message
DVB
Power User
Power User


Joined: 01 Feb 2008
Posts: 99

PostPosted: Tue Feb 03, 2009 12:19 pm    Post subject: Reply with quote

Thanks for your reply. Should I understand from this that I'm not actually creating an OOo database at all, but just a query link to the original MS Access database? Isn't there a way of creating an actual OOo database from the data in the Access database?

I don't see any button for a filter. The problem is that when I'm in the screen where I can choose Tables, Queries, Forms or Reports based on selected fields, the left hand box labelled "Available fields" has nothing in it. Without a list of fields to select from, I can't select any fields to go in the right hand box, "Fields in the Query/Form/Report" -- and so I can't access anything in the database.
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Tue Feb 03, 2009 1:40 pm    Post subject: Reply with quote

Hello

First it must be clear what happens with your records. That is most important.

You are talking about the screen when you the database opens.
You see
left hand : tables,query,form,report

Quote:
I can't select any fields to go in the right hand box, "Fields in the Query/Form/Report" -- and so I can't access anything in the database.

Correct They only show it as a thumbnail picture


select tables
select the table who you want
double click on the table name; not in the right window

Now you see your table with all the visible columns. But it is maybe filtered.

Howto make them visible as I hope.

Go to the top of the window. There you see several symbols
On my computer just below "file" there is an icon for "save current record" go with the mouse to that icon follow the row to the left and nearly at the end there is an icon for filter symbol with a cross. When the mouse is above then you can read "remove filter /sort" when you click on this symbol then the filter is delete not the records. The filter can also be sorting. If the filter is greyout and you don't see the records then you must hope that you have make a copy.

I hope that your records are now complete visible. In the same window you can learn how to hide and make visible columns.
Go with the mouse on the column header right click and you can hide and make visible columns.

I wait with the other questions until you see the records. And maybe I found some other post where I can point you.

Maybe if you search on this forum look for post of Villeroy of Voobase, If you have question about SQL, search for Sliderule

I hope you see your records.
Romke
Back to top
View user's profile Send private message
DVB
Power User
Power User


Joined: 01 Feb 2008
Posts: 99

PostPosted: Wed Feb 04, 2009 2:45 am    Post subject: Reply with quote

No joy. For a start, the Tables wizard offers me Sample Tables which have nothing to do with my database, under two categories, Business and Personal, e.g. Assets, Deliveries, Employees, or CD-Collection, DietLog, Plants etc. Even if I select one of these I don't get anything like you describe; it gives me a bunch of Available Fields, but they're not the fields of my own database. There is no icon for "save current record".

I couldn't make any sense of Tables on Monday, because of its imposition of irrelevant categories. At least with Queries, Forms and Reports the left hand panel showed the real field names from my already-existing database. Now they don't -- the left hand panel is empty.

And I can't find anything approaching your description about icons or filters or removing them. The only thing I can see is at the very top of the OOo Base screen, in the Toolbar: Tools > Tables Filter, which has a tick box next to "All tables". Is that what you're talking about? Because it makes absolutely no difference whether I tick the box or not.

What really puzzles me is that I'm creating this .odb database afresh from the .mdb original, by launching Base and following the instructions to Connect to an Existing Database. On Monday, when I first did this, the 120+ field names from my database were visible in the left hand panel. Now they're not. I'm hoping that I haven't destroyed the contents of the .mdb database.
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Wed Feb 04, 2009 5:27 am    Post subject: Reply with quote

Hello

I do understand: You see nothing more of your database, not even the table name!

With the first post I did think your records are still there. Maybe I must have it read more careful, but when people start with OOo they can not find back data who are on the computer.

You did use your *.mdb file for creating for the *.odb file what then happens I don't know. Then it is possible that your table also is destroyed special you did delete the new *.odb file. I had over read that you also had have delete your *.odb file.

Now you may hope that you have a copy of your original *.mdb file on your computer or somewhere else.

Maybe other people can say I'm wrong.
I is not nice and I feel sorry for you that I have give you false hope that the records maybe there

Romke
Back to top
View user's profile Send private message
DVB
Power User
Power User


Joined: 01 Feb 2008
Posts: 99

PostPosted: Wed Feb 04, 2009 7:03 am    Post subject: Reply with quote

Thanks for trying to help, RPG. You gave me a clue what to try next:
Quote:
Now you may hope that you have a copy of your original *.mdb file on your computer or somewhere else.

Fortunately I did. I made sure I took another copy for safety, then started again. The fields reappeared, and I've been able to create the first report I needed -- and am currently printing out all 68 pages of it!

Thanks again.
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Sat Feb 07, 2009 5:57 am    Post subject: Reply with quote

Hello

I did skip the question if OOo_base is a kind of link. I will try now to answer this question. If you want have more detailed answers follow the links in this post.

OOo_base is a container file that means it have more then one file in it. You can not even compare it with a zip file, it is a zip. If you name.odb change in name.zip and then unzip the file you see several other file.

What file are in this container file
always:
Your forms
Your queries
Your reports

not always:
Your tables
Your views
Your relations

For the most of us all files are in the container file, but I prefer to have it separated;I think it is more save. Several people have loose sometimes files or records with OOo-base. Saving files who belong to the database is also changed from version to version. It means that with a higher version there is less change too loose your data.
There is a different in saving between the forms , queries and reports and on the other hand the records.

What is the best way for closing your database
close first your forms, queries and reports.
close the OOo-base file. In newer version sometimes you be ask for saving the files who belong to the OOo_base file.

The table
Most of the time there is no problem but if you have work an hour with inputting records, there is a change that you loose all your work you have done in case of a crashes. For saving the records you have to go to the windows which you see when you open an name.odb file
menu --> tools -->SQL
Then there comes a popup
type there :CHECKPOINT DEFRAG and click on "execute"

If you notice that:
1)Your OOobase-file is growing even is you delete records
2)It takes longer times the opens the database.
Then it is maybe time to use
SHUTDOWN COMPACT
After this you have to close your name.odb and restart it

For more detailed information look in the links below.

Romke


Links about defrag and shutdown
http://www.oooforum.org/forum/viewtopic.phtml?t=52208&highlight=checkpoint
http://www.oooforum.org/forum/viewtopic.phtml?t=70654&highlight=checkpoint
http://www.oooforum.org/forum/viewtopic.phtml?t=70654&highlight=checkpoint

about something else
http://user.services.openoffice.org/en/forum/viewtopic.php?f=13&t=14962
Back to top
View user's profile Send private message
DVB
Power User
Power User


Joined: 01 Feb 2008
Posts: 99

PostPosted: Sat Feb 07, 2009 6:05 am    Post subject: Reply with quote

Thanks for such a detailed response.
It sounds as if OOo haven't yet got their database s/w in a safely usable state. I'll take care with its use, and make sure I always have a saved copy of the original .mdb file.

David
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group