Help needed changing from Access + asp to OO Base + mySQL + php

[rik1301]

Hello all,

I'm trying to move platform from Windows to Linux but my damned database in MS Access is the last hurdle stopping me from completing my mission! Changing/converting the database from mdb to odb format is not causing me any problems, but what is causing me big problems is that the database is hosted live on my website for people to search and the web page is in asp.

Having very little knowledge of webdev I thought I may be able to make it work by creating a replica test database in Base (odb) and then uploading that to my ftp, changing the database filename from whatever.mdb to whatever.odb and then maybe it would work. Unsurprisingly, it didn't... It gave me some Microsoft JET Engine database error and "Unrecognized database format". I've since discovered that odb files cannot be used in this way and I've been advised to convert my mdb file to mysql which will then work with Base and then build a new database search page in php.

I really have no idea where to start with any of this. I read a handful of tutorials on mysql about creating a new database and I get the general idea but it all seems so complicated. With Access it's literally a 2 min job to add some new records to the database, save it, open filezilla and then drag and drop the mdb file and job done. All this saving, exporting and converting to mysql and making server connections seems complete overkill to me and sounds like it'll take hours to do every time I make any database changes.

Does anyone know of an easier and simpler way of doing this? My main problem is making the script for the web page so it can 'talk' to the database and display the results.

My website is [snip] and the relevant bit of the current script is here -

<FORM METHOD="GET" ACTION="default.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("/") & "\selcalsrik130177.mdb;"
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("/") & "\selcalsrik130177.mdb;"
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("/") & "\selcalsrik130177.mdb;"
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
%>

Really hope someone can help me with this. Thanks all.

[edited by: brotherhood_of_LAN at 12:04 pm (utc) on May 20, 2010]
[edit reason] removed personal url as per the ToS [/edit]

[brotherhood of LAN]

Hello rik1301 and welcome to the forums.

Have you had any progress with this the past couple of days? I assume you want to convert the access DB to MySQL to fully migrate to your linux server...

It's probably easiest to take this step by step. Exporting your database to a csv format would probably make it more portable. Do you if you have cpanel or phpmyadmin on its own on the linux server?