Difference between revisions of "MSA-Base Faq"
(→Can I create an Access database (mdb file) with Base?) |
DrewJensen (Talk | contribs) (→Is a Form in Base different from a Form in MSA?) |
||
Line 35: | Line 35: | ||
== Is a Form in Base different from a Form in MSA? == | == Is a Form in Base different from a Form in MSA? == | ||
− | + | Yes | |
There are very real differnces between how Forms are implemented in Access and Base. | There are very real differnces between how Forms are implemented in Access and Base. |
Revision as of 13:02, 28 September 2006
Common questions and answers for those migrating from MS Access to OO.o Base.
This is a user community effort - add to the knowledge base if you can.
Please however try to keep the questions and answers general. If there is an answer regarding a feature specifc version of MSA then include the version number also.
Specifically, please only update the wiki if you have an answer for the question you are adding. ( If you may need to get back shortly with the answer don't let that stop you from getting the queston in first however - just come back before long )
For asking specific questions or for general help it would be appropriate to then ask on either the: Base Forum or on the Base users mailing list.
Contents
- 1 Can I read my Base database from MS Access?
- 2 Can I create an Access database (mdb file) with Base?
- 3 Can I create a Switchboard in Base?
- 4 Is a Form in Base different from a Form in MSA?
- 5 Where are the Delete / Upate queries?
- 6 I want to filter my form based on the contents of a combobox?
- 7 Converting my VBA modules to Base modules
Can I read my Base database from MS Access?
There is no way to use the table linking mechanisim in Access to connect to a Base database.
It is possible for a Base database to update data in the Access database. At this time this will require however that the Access database be opened and locked by the Base file.
Data could also be passed back to the Access database by creating a linked CSV file in the Base database. Then linking to this CSV file to a table in the Access database.
Can I create an Access database (mdb file) with Base?
No. Base can not create a new Access database file; or new tables, queries, forms etc. within an existing mdb file.
Can I create a Switchboard in Base?
Base does not currently have a switchboard style form wizard. It is possible to achieve a very similar effect however using either of two techinques.
Opening a default form via a macro
Using a stand alone form to launch the Base database
Is a Form in Base different from a Form in MSA?
Yes
There are very real differnces between how Forms are implemented in Access and Base.
Virtual forms within Base forms
Where are the Delete / Upate queries?
Base does not currently support building delete / insert / update queries in the desinger.
equivilant actions
using the Query Designer
You can achieve the equivilant actions in the GUI using the designer and or SQL commands.
using a macro
I want to filter my form based on the contents of a combobox?
Converting my VBA modules to Base modules
What happened to me?
There is no exact duplicate to the ME psuedo variable in VBA.
In Access VBA 'me' has attributes of two seperate objects in OOoBasic: TextDocument and DataForm
The TextDocument object would be used for controlling the window size and placement on the screen for instance.
The DataForm meanwhile supports result set functions, such as First, Filter, InsertRow.
In OOoBasic the psuedo variable thisComponent normaly would be the TextDocument, while thisComponent.DrawPage.Forms(0) meanwhile would reference the first DataForm owned by the TextDocument.
Perhaps a still closer equivilant is to use the object ownership hierarchy to get to the form. For example if we had a single table form with a button, then in the macro called by the 'onClick' or as OOBase calls it the 'When initializing' event we would do this: Create a macro in a library named onButtonClick( oEv as object )
sub onButtonClick( oEv as object ) ' oEv is the event object passed automatially ' it has a property SOURCE which is the ' button that initiated this call dim oForm as variant oForm = oEv.Source.Model.Parent ' oForm is now the virtual form objct dim oDocument as variant oDocument = thisComponent ' oDocument is now the textDocument displayed in ' the window on screen end sub
Where is findfirst?
Base does not appear to have a FindFirst function on the rowset like MS Access, what is the equivilant Base function?
MSAccess VBA example:
Say there is a VB combo box which lists all the prospects in the database and allows the user to select a particular prospect and then have the form automatically go to that person's data record.
Private Sub Find_Combo_AfterUpdate() Dim strCriteria As String strCriteria = "[Prospect_ID] =" & Me.Find_Combo Me.recordsetclone.FindFirst (strCriteria) If me.recordsetclone.NoMatch Then MsgBox "No entry found" Else Form_F_Prospects.Bookmark = me.recordsetclone.Bookmark End If End Sub
For this type of simple single column search it is easy enough to create our own find first equivilant with OOoBasic:
Sub Find_Combo_AfterUpdate( OEv as Object) ' Oev is the event object automatically sent ' to sub procedure call ' Oev will have a property Source ' that is the control that triggered ' the call to this procedure Dim oForm as variant ' The dataform obejct Dim oComboBox as variant ' The combobox control Dim oBkMark as variant oForm = oEv.Source.Model.Parent oComboBox = oForm.getByName( "Find_Combo" ) oBkMark = FindFirst( oForm.CreateResultSet(), "Prospect_ID", oComboBox.Text ) ' oForm.CreateResultSet() is equivilant here to me.recordsetclone if not IsEmpty( oBkMark ) then oForm.moveToBookmark( oBkMark ) else msgBox( oComboBox.Text & " not found" ) end if end sub
function FindFirst( oRS as variant, ColName as string, SearchVal as string ) as variant ' oRS is a resultSet object ' ColName is the name of a column in the resultset ' SearchVal is dim colIDX as integer dim NotFound as variant FindFirst = notFound colIDX = oRS.FindColumn( ColName ) oRS.First do if oRS.getString( colIDX ) <> Searchval oRS.Next if oRS.isAfterLast then exit do endif else FindFirst = oRS.getBookMark exit do end if while oRS.isAfterLast <> True end function
How do I set the filter for a sub-form only?
Is there an equivilant to DoCmd RunSQL
Yes, in OOoBase you would use either a Statement or PreparedStatement object to achieve the same function. With OOoBase you must also decide between using execute and executeUpdate.
For example if you had the VBA line
DoCmd RunSQL( "SELECT * FROM tContacts" )
it would become in OOoBase
Dim oStatement as variant oStatement = OForm.ActiveConnection.CreateStatement ' Where oForm is a DataForm object ' NOTE you could do exactly the same thing with ' a resultSet or rowSet obejct also ' oStatement = OResultSet.ActiveConnection.CreateStatement oStatement.execute( "SELECT * FROM ""tContacts""" )
If on the other hand the VBA line where
DoCmd RunSQL( "DELETE FROM tContacts WHERE Status = 'INACTIVE'" )
then we would use the executeUpdate method on a statement
oStatement.executeUpdate( "DELETE FROM ""tContacts"" WHERE ""Status"" = 'INACTIVE'" )
The rule is -
- if your SQL statement will return records you use execute or executeQuery.
- if the SQL statement returns no records then you must use executeUpdate.
If I use DLookup columns in MS Access what can I do in Base?
There are two parts to this answer, given that Base does not support DLookup column types in tables.
When a table is displayed in a custom form use of a bound listbox will give you the same functionality. An example of adding a listbox to a form and to a grid control on a form is at this page.
In a table view ( double clicking on the table name ) there is no equivilant to this.