loading vars from an access db

[pwrstick]

I'm trying to load dates from an Access database into a flash file. The DB is on a server other than the page that hosts the flash file, and I've learned that I can't load vars accross domains because of security reasons.

So I'm thinking I'll have Access output a text file that I'll upload to the server, and then have Flash load.

But is there other software that will do this automatically?

Thanks for your help!
-phil

[nraven]

There might be a few ways to address this problem. Some more information would be helpful, though. When you say domains, are you referring to NT/Win2k security domains? Do both servers reside on the same network? Will the flash application need to be externally accessible?

[SuperMario]

You can easily run an ASP script that will read from a DB based on an ID number (or something) that you feed it and create a text file.

I would do something like this in ASP:

<%
   ' ==============
   ' createfile.asp
   ' Queries the Access DB and outputs a text file
   ' ==============

   Dim nID ' The ID number you'll use to find DB info
   
   ' Set nID equal to a Request item
   nID = Request("nID")

   Dim rs, conn ' Recordset and Connection objects
   Set rs = Server.CreateObject("ADODB.Recordset")
   Set conn = Server.CreateObject("ADODB.Connection")

   conn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("my-database.mdb")
   
   rs.Open "SELECT Name, Address FROM People WHERE ID = " & nID, conn, 2, 3

   ' Create Text File Stuff
   Dim objFSO, txt ' FileSystemObject, Text file
   Dim strToWrite ' String to write to file
   Dim strFilePath ' Unique file path

   Set objFSO = CreateObject("Scripting.FileSystemObject")

   strFilePath = rs("ID") & ".txt"

   Set txt = objFSO.CreateTextFile Server.MapPath(strFilePath), True

   strToWrite = "ID=" & rs("ID") & _ 
   "&Name=" & rs("Name") & _
   "&Address=" & rs("Address")

   strToWrite = Server.URLEncode(strToWrite)
   
   txt.WriteLine(strToWrite)

   txt.Close
   Set txt = Nothing
   Set objFSO = Nothing

   rs.Close
   Set rs = Nothing
   conn.Close
   Set conn = Nothing
   
   Response.Write "<A HREF=" & Chr(34) & strFilePath & Chr(34) & ">" & strFilePath & "</A>"
%>

Basically what this does is:

1) You type in: http://localhost/createfile.asp?nID=2 into your web browser.

2) It accesses this script and tells it you want to create a Flash-compatible text file from record number 2.

3) The script opens the database, reads the data, and encodes it, then places it in a text file called ID.txt, where ID is the actual record ID (primary key) so you don't get duplicate text files.

4) It outputs a link to the file so you can save it to your HD.

Hopefully this helps you!

-Dan

[roele]

or to read directly from (dns-less) database
use this script:

<%@LANGUAGE=VBScript%>
<%RESPONSE.Buffer=True 'Seite preloaden %>
<%RESPONSE.Expires=0 'nicht chachen ! %>
<%'ansprechen der Datenbank via ActiveX Object DNS-Los (ADODB =Schnittstelle)%>
<object id="con" ProgID="ADODB.Connection" Runat="Server"></object>
<object id="rs" ProgID="ADODB.Recordset" Runat="Server"></object>
<%
action = Request("action")
answer = Request("answer")
if action = "rd" Then
RD()
elseif action = "sg" Then
SG()
else
message = "No query string entered"
response.write ("&message=" +URLEncode(message)+"&cconfirm=1")
response.end
End If

Function SG()
DIM strCon 'variable Definieren
strCon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Request.ServerVariables("APPL_PHYSICAL_PA TH")+"webdesigner\schaer\data\db1.mdb"
con.Open strCon 'connection öffnen
strSql="Select * From tblQuestion"
rs.Open strSql, con, 3, 3

If answer="1" Then
rs("answer1Clicks")=rs("answer1Clicks")+1 'Feldwert um 1 erhöhen response.write ("&loaded=1")
ElseIf answer="2" Then
rs("answer2Clicks")=rs("answer2Clicks")+1
response.write ("&loaded=1")
ElseIf answer="3" Then
rs("answer3Clicks")=rs("answer3Clicks")+1
response.write ("&loaded=1")
End If
rs("clicksTotal")=rs("clicksTotal")+1
rs.Update
rs.Close


End Function


Function RD()

DIM strCon 'variable Definieren
strCon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Request.ServerVariables("APPL_PHYSICAL_PA TH")+"webdesigner\schaer\data\db1.mdb"
con.Open strCon 'connection öffnen
strSql="Select * From tblQuestion"
rs.Open strSql, con, 3, 3

response.write ("&question" & "="+ rs("question"))
response.write ("&answer1" & "="+ rs("answer1"))
response.write ("&answer2" & "="+ rs("answer2"))
response.write ("&answer3" & "="+ rs("answer3"))

response.write ("&clicksTotal" & "="+ rs("clicksTotal"))
response.write ("&answer1Clicks" & "="+ rs("answer1Clicks"))
response.write ("&answer2Clicks" & "="+ rs("answer2Clicks"))
response.write ("&answer3Clicks" & "="+ rs("answer3Clicks"))
response.write ("&loaded=1")

End Function

%>

for reading just add to buttonaction
on(press){
loadVariables("question.asp?action=RD,0,"POST");
}

I used it for a voteSystem (www.uniresear.ch/webdesigner/schaer/html/question.html)