Connecting to client MDB

[markrhodes]

I have an database locally in about 250 locations. I want to write a ASP page that will lookup a persons Social Security number on a SQL server. Then allow me to write the data to a local database on the workstation. The remote users are not always connected (and when they are its only 56k) so they need their records local.

Summary
Basically can I connect to an MDB on a clients (obviously these are all my own clients) computer from an ASP page with VBScript or Jscript? The MDB is the same name and location on all machines.

 

[snowboardr]

What about a client side program the sends data? I don't know about connecting to db on there system..if your only sending a little bit of data I don't think there speed matters.

I don't know if that helps but thats my 2 cents.


-Jason H

 

[harmmeijer]

This wil connect to an access database on the client using client side scripting:

<script language=VBScript>
dim rs
set rs = createobject(&quot;ADODB.Recordset&quot
rs.Open &quot;select * from authors&quot;, &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Biblio.mdb;Persist Security Info=False&quot;, 2, 3
msgbox rs.Fields(1)
</script>

If you want to pump the data from the SQL database to the client you can let the asp page generate the script when the client is on the intranet and opens the asp page.
It wil look something like this:

<%
Response.Buffer = true
Response.Expires = -1
dim rs
set rs = createobject(&quot;ADODB.Recordset&quot
' your script will open a recordset from the sql server
rs.Open &quot;select * from authors&quot;, &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Biblio.mdb;Persist Security Info=False&quot;, 2, 3
with response
' now generate the client script:
.Write &quot;<script language=VBScript>&quot; & vbcrlf
.Write &quot; dim rs&quot; & vbcrlf
.Write &quot; set rs = createobject(&quot; & chr(34) & &quot;ADODB.Recordset&quot; & chr(34) & &quot&quot; & vbcrlf
.Write &quot; rs.Open &quot; & chr(34) & &quot;select * from authors&quot; & chr(34) & &quot;, &quot; & chr(34) & &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Biblio.mdb;Persist Security Info=False&quot; & chr(34) & &quot;, 2, 3&quot; & vbcrlf
' now put all the records from the servers recordset into the client recordset
i = 0
' in this case I will only use the first 10 records
' do until rs.EOF
do until i = 10
.Write &quot; rs.AddNew&quot; & vbcrlf
.Write &quot; rs.fields(1) = &quot; & chr(34) & replace(rs.Fields(1),&quot;'&quot;,&quot;''&quot & chr(34) & vbcrlf
.Write &quot; ' put in any of the fields you need, remember to replace the ' with ''&quot; & vbcrlf
.Write &quot; rs.update&quot; & vbcrlf
i = i + 1
rs.MoveNext
loop
.Write &quot;msgbox &quot; & chr(34) & &quot;finished inporting&quot; & chr(34) & vbcrlf
.Write &quot;</script>&quot; & vbcrlf
end with
%>