Some questions/advice wanted: MSA > Base + website

[rik1301]

Hello all,

I feel a bit out of place here as I'm an XP user and have only dipped my toes into the world of Linux very briefly thus far - my friend has Ubuntu on his system and I'm liking it more and more everytime I use it. I have been mulling over installing it here and changing to it permanently but my Access database is holding me back and I really don't want the hassle of using Ubuntu as my main OS but still needing to have Windows to run my database, so I'm hoping you guys can tell me there are 'solutions' and without too much stress .

My basic questions are -

1. Can I easily import my database into Base and it works without any issues ?
2. Will the Base format work with my website database search page ?

My database contains roughly 26,000 records across 10 columns and is 3.2 meg in size, compacted. I don't use any of the queries/forms/reports though, just the 'normal' database page.

As far as the web database search goes, I upload the saved .mdb file to my FTP and visitors can search data within 3 out of the 10 columns by entering the criteria into a search box. I'm not sure what the tech jargon is for it, but it's an .asp page and uses some ODBC stuff in the page coding.

Assuming that the Access file can be imported/converted into Base, then is it just a simple case of changing the filename extension in my web coding page from .mdb to whatever Base' file extension is or is it a lot more complicated? Does a step by step guide exist anywhere for doing this?

Thanks for reading and look forward to your replies.

[Villeroy]

Base can connect to your database just like Access connects to some third party database. You get access to the raw table data. That's it.
Base is not a database program. It can access many databases, serving data to spreadsheets and text documents. Apart from dBase files it can not produce anything which would work as a database for the outside world.

[rik1301]

Villeroy - thanks, but I think you have misunderstood me. I do not want to just access a .mdb file in Base, I was to transfer the whole thing to Base and use Ubuntu as my OS and ditch XP and Access altogether. I am aware that you cannot edit the records in Base when you open the .mdb file in it, but that is not what I want to do. I want to convert my .mdb to .odb. I have since read another thread on these forums and most users said that it would work (http://user.services.openoffice.org/en/ ... =13&t=6201) so I don't understand why you say it does not. Can you explain?

That aside for now, I would still like to know if uploading the new .odb file to my FTP will work with my GET form method for searching for stuff in my database on my web database search page or will some other changes need to be done? Anyone know?

Thanks.

[Villeroy]

Like the rest of this office suite Base has nothing to do with web content. I pulls data from databases into office documents. It provides simple forms to edit data across relations. You don't have write access to tables without primary key.
[Tutorial] Read-Only in Base

[ponlerd]

็Hi, i have the same experience here, maybe i can point you to some directions.
Migrating from Access to Base without Windows
First step : on XP, Migrate your back end database from access to mysql (or the like)
Second step : on XP, Try to see if you can use ms access as a front end to your back end on mysql successfully
**problems that i've found before to get it working
-all tables must have primary keys
-all tables must have timestamps
-configure ODBC mysql connector to "Return matched rows instead of affected rows"

Third step : on XP, you can still use your ms access front end to do your daily work
at the same time, start re-writing all forms , reports and VBA on Base /Linux, while connecting to the mysql database

Fourth step : Once successful, you can now stop using ms access on xp and fully use base/linux
i've done all four steps now, hope it helps

[rik1301]

Hi,

I can work around any issues importing/converting the mdb to odb as I've read online that you can export the mdb to xls and then import the file into odb format that way. I'm sure there is some work-around there anyway.

The main problem I am having at present is getting my asp web page to recognise the odb file format. I've just created a test database in Base with exactly the same tables as my mdb database and input some test data. I've uploaded it to my ftp, changed the file name in the asp script to link to the new .odb file I've uploaded but when I do a search for data on my live page I'm getting the following error message -

Microsoft JET Database Engine error '80004005'

Unrecognized database format 'blah.odb'

/default1.asp, line 144

--

I'm afraid my knowledge of how scripts work to get the data and show the results is extremely limited, but I'm guessing that the script I have only works for mdb files and not other file types. I'm going to include an extract of the asp script below - I'd be really grateful if someone can tell me what I need to do/change to make it work.

I can't highlight in code format, but line 144 is the one where I've added a bunch of asterisks after it.

Code: Select all

<FORM METHOD="GET" ACTION="default1.asp" NAME="searchDB">
  <div align="center"> 
    <table align="center">
      <td width="54"><font size="2" face="Verdana">SEL:</font></td>
      <td width="209"><input type="text" name="sel" size="14" maxlength="255">
        <font size="2" face="Verdana"><em>(eg. BPCH)</em></font></td>
      <tr> 
        <td><font size="2" face="Verdana">REG:&nbsp;</font></td>
        <td><input type="text" name="reg" size="14" 
maxlength="255">
        <font size="2" face="Verdana"><em>(eg. G-BNLL)</em></font></td>
      <tr> 
        <td><font size="2" face="Verdana">MSN:&nbsp;</font></td>
        <td><input type="text" name="msn" size="14" 
maxlength="255">
        <font size="2" face="Verdana"><em>(eg. 24054)</em></font></td>
      <tr> 
        <td><input name="submit" type="submit" value="search"></td>
        <td>&nbsp;</td>
    </table>
  </div>
</FORM>
<p align="left"> 
  <%
If Trim(Request.QueryString("sel")) <> "" Then
getSelCal(Request.QueryString("sel"))
ElseIf Trim(Request.QueryString("reg")) <> "" Then
getRego(Request.QueryString("reg"))
ElseIf Trim(Request.QueryString("msn")) <> "" Then
getCn(Request.QueryString("msn"))
Else
End If



Private Function getSelCal(qString)
Dim cn
Dim rs1
Dim SQLString
Dim s
Dim found

SQLString = "SELECT * FROM selcals WHERE sel='" & qString & "';"
set cn = Server.CreateObject("ADODB.connection") 
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;data source=" & Server.MapPath("/") & "\SELCALS.odb;"     ******************
Set rs1 = Server.CreateObject("ADODB.Recordset")
rs1.Open SQLString, cn, 1, 2
If rs1.EOF = True And rs1.BOF = True Then
found = False
Else
Response.Write("<TABLE STYLE=""font-family:verdana; font-size:12px""><TABLE CELLPADDING=""4"" CELLSPACING=""0"" BORDER=""1"">")
Response.Write("<TR><TD STYLE=""font-family:verdana; font-size:12px""><B>SEL</B></TD><TD STYLE=""font-family:verdana; font-size:12px""><B>REG</B></TD><TD STYLE=""font-family:verdana; font-size:12px""><B>OPR</B></TD><TD STYLE=""font-family:verdana; font-size:12px""><B>TYPE</B></TD><TD STYLE=""font-family:verdana; font-size:12px""><B>MSN</B></TD><TD STYLE=""font-family:verdana; font-size:12px""><B>H</B></TD><TD STYLE=""font-family:verdana; font-size:12px""><B>EX/OPERATOR/STATUS/NOTES</B></TD></TR>")
Do Until rs1.EOF = True
s = "<TD STYLE=""font-family:verdana; font-size:12px"">" & rs1("sel") & "</TD><TD STYLE=""font-family:verdana; font-size:12px"">" & rs1("reg") & "</TD><TD STYLE=""font-family:verdana; font-size:12px"">" & rs1("opr") & "</TD><TD STYLE=""font-family:verdana; font-size:12px"">" & rs1("type") & "</TD><TD STYLE=""font-family:verdana; font-size:12px"">" & rs1("MSN") & "</TD><TD STYLE=""font-family:verdana; font-size:12px"">" & rs1("H") & "</TD><TD STYLE=""font-family:verdana; font-size:12px"">" & rs1("ex/operator/status/notes")  & "</TD>"
Response.Write("<TR>" & s & "</TR>")
rs1.moveNext
Loop
Response.Write("</TABLE>")
found = True
End If
rs1.Close
Set rs1 = Nothing
cn.Close
Set cn=Nothing
End Function


Private Function getRego(qString)
Dim cn
Dim rs1
Dim SQLString
Dim s
Dim found

SQLString = "SELECT * FROM selcals WHERE reg='" & qString & "';"
set cn = Server.CreateObject("ADODB.connection") 
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;data source=" & Server.MapPath("/") & "\SELCALS.odb;"
Set rs1 = Server.CreateObject("ADODB.Recordset")
rs1.Open SQLString, cn, 1, 2
If rs1.EOF = True And rs1.BOF = True Then
found = False
Else
Response.Write("<TABLE STYLE=""font-family:verdana; font-size:12px""><TABLE CELLPADDING=""4"" CELLSPACING=""0"" BORDER=""1"">")
Response.Write("<TR><TD STYLE=""font-family:verdana; font-size:12px""><B>SEL</B></TD><TD STYLE=""font-family:verdana; font-size:12px""><B>REG</B></TD><TD STYLE=""font-family:verdana; font-size:12px""><B>OPR</B></TD><TD STYLE=""font-family:verdana; font-size:12px""><B>TYPE</B></TD><TD STYLE=""font-family:verdana; font-size:12px""><B>MSN</B></TD><TD STYLE=""font-family:verdana; font-size:12px""><B>H</B></TD><TD STYLE=""font-family:verdana; font-size:12px""><B>EX/OPERATOR/STATUS/NOTES</B></TD></TR>")
Do Until rs1.EOF = True
s = "<TD STYLE=""font-family:verdana; font-size:12px"">" & rs1("sel") & "</TD><TD STYLE=""font-family:verdana; font-size:12px"">" & rs1("reg") & "</TD><TD STYLE=""font-family:verdana; font-size:12px"">" & rs1("opr") & "</TD><TD STYLE=""font-family:verdana; font-size:12px"">" & rs1("type") & "</TD><TD STYLE=""font-family:verdana; font-size:12px"">" & rs1("MSN") & "</TD><TD STYLE=""font-family:verdana; font-size:12px"">" & rs1("H") & "</TD><TD STYLE=""font-family:verdana; font-size:12px"">" & rs1("ex/operator/status/notes")  & "</TD>"
Response.Write("<TR>" & s & "</TR>")
rs1.moveNext
Loop
Response.Write("</TABLE>")
found = True
End If
rs1.Close
Set rs1 = Nothing
cn.Close
Set cn=Nothing
End Function 


Private Function getCn(qString)
Dim cn
Dim rs1
Dim SQLString
Dim s
Dim found

SQLString = "SELECT * FROM selcals WHERE msn='" & qString & "';"
set cn = Server.CreateObject("ADODB.connection") 
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;data source=" & Server.MapPath("/") & "\SELCALS.odb;"
Set rs1 = Server.CreateObject("ADODB.Recordset")
rs1.Open SQLString, cn, 1, 2
If rs1.EOF = True And rs1.BOF = True Then
found = False
Else
Response.Write("<TABLE STYLE=""font-family:verdana; font-size:12px""><TABLE CELLPADDING=""4"" CELLSPACING=""0"" BORDER=""1"">")
Response.Write("<TR><TD STYLE=""font-family:verdana; font-size:12px""><B>SEL</B></TD><TD STYLE=""font-family:verdana; font-size:12px""><B>REG</B></TD><TD STYLE=""font-family:verdana; font-size:12px""><B>OPR</B></TD><TD STYLE=""font-family:verdana; font-size:12px""><B>TYPE</B></TD><TD STYLE=""font-family:verdana; font-size:12px""><B>MSN</B></TD><TD STYLE=""font-family:verdana; font-size:12px""><B>H</B></TD><TD STYLE=""font-family:verdana; font-size:12px""><B>EX/OPERATOR/STATUS/NOTES</B></TD></TR>")
Do Until rs1.EOF = True
s = "<TD STYLE=""font-family:verdana; font-size:12px"">" & rs1("sel") & "</TD><TD STYLE=""font-family:verdana; font-size:12px"">" & rs1("reg") & "</TD><TD STYLE=""font-family:verdana; font-size:12px"">" & rs1("opr") & "</TD><TD STYLE=""font-family:verdana; font-size:12px"">" & rs1("type") & "</TD><TD STYLE=""font-family:verdana; font-size:12px"">" & rs1("MSN") & "</TD><TD STYLE=""font-family:verdana; font-size:12px"">" & rs1("H") & "</TD><TD STYLE=""font-family:verdana; font-size:12px"">" & rs1("ex/operator/status/notes")  & "</TD>"
Response.Write("<TR>" & s & "</TR>")
rs1.moveNext
Loop
Response.Write("</TABLE>")
found = True
End If
rs1.Close
Set rs1 = Nothing
cn.Close
Set cn=Nothing
End Function
%>
  <br>
</p>

I really hope someone can help? This damned Access db is the only thing that's stopping me from changing from Windows to Linux.

Thanks.

[ponlerd]

I don't have any knowledge about asp BUT(a big but) i don't think that you can connect anything to .odb database.
Maybe I'm wrong because i have read some posts about making a .odb database a back end , but i don't think it's going to work with acceptable speed anyways.
I'm guessing that you haven't transferred your database to mysql or other real database yet, i think that's the step you have to do because
a .odb database cannot(i might be wrong) be a back end for anything else other than .odb forms and reports. ( and i believe cannot be a backend for any web based front ends.)
if you have your backend in a real database, then you can connect to the database by using any front end.
** .odb base should be used as a front end , not a backend other than your own single user database for small jobs.

[rik1301]

Ponlerd, thanks. I understand now what Villeroy was saying. I'm in really unknown territory with all this so not sure what I'm doing.

Let's look at it from the opposite end and forget the mdb for now. If I have an odb database, what is the best way for people to search for data in it via a search page on my website?

Thanks.

[ponlerd]

i think it's impossible(maybe i'm wrong but i'm quite sure that even it's possible, it won't be a good working website)... and if you're hosting a real website ( i mean on the internet, not a local website on a LAN) , i really think u have to migrate your data to a real database. and ooo base is not that thing. If you really want to stop using ms access windows, i think you have to start looking at LAMP (Linux , Apache, Mysql, php)

[evwool]

I've just had a go at transferring a 28000+ record database from Access into Base. Yes, I got it to import but it performs like a total slug. Long waits before a single table opened and that's even without me having put in forms with list boxes. I've now re-installed MySQL Community Server using the instructions here
http://www.sitepoint.com/blogs/2009/03/ ... all-mysql/ and using the No Installer version (much simpler than using the Windows Installer). I created a database and tables with MySql, connected to it using Base and I'm currently copying across my data by pasting first into Excel and then pasting it into the SQL table. It's a long slow business but it seems to be working so far (I may have to paste my longest table a bit at a time). I'll let you know how it works.

Edit: It doesn't work if I use Excel but it seem to work fine (though very slowly) from Calc. It didn't work at all when I tried dragging tables from Base or Access (Column types not recognised

Edit: I've got all the data pasted in - 3 main tables with about 28000 + records each, plus a few small lookup tables. It was certainly better than HSBSQL and the individual tables opened pretty well instantly but now I've created a query in the Base window which links the 3 tables and it took about 3 minutes to open. That's without adding on any of the lookup tables to the mix. The same query in Access, with all the extra lookup tables, opened instantly. Disappointing