Add records to a MS Access web database (2K)

Topic

New Reply

Is there a way to add new records to an intranet access database from a workstation access database.
I’m able to automatically download data from the intranet db when the workstation db opens using vba & asp examples that I found on the web.
I can’t figure out how to add records. Can someone point me in the right direction.

Reply | Quote

Replies

I am assuming that you have the ability to create an asp page on the Intranet in question? The next question is are we talking about one record, or are we talking about a bunch of records. I ask, because if you are just pushing one record, and the data isn’t going to exceed a thousand (and change) characters, then you can just ‘open’ a link with the data in a querystring. But if you are pushing lots of records, you’ll need to ‘POST’ the data, through a form.

Reply | Quote

Drew,
I have control of the intranet server. The amount of data will vary depending on the user that connects. A bunch? Shouldn’t be more than 100 records at a time. Unfortunately ther are 86 fields in the table(not my design).You mention POST through a form. Is that in access or a web based form?
With the table info I gave you, let me know the best way.
Thanks

Reply | Quote

Make a function that creates an HTML page with a form that posts the data that you want…

Function CreateHTMLForm()
Dim strTemp As String
Dim f As Long
Dim rs As Recordset
Dim strSQL as String
dim i as Long
Dim j as Long
strSQL=”SELECT * FROM MyTable”
set rs=CurrentDB.OpenRecordset(strSQL)
rs.MoveFirst
strTemp=”” & vbcrlf
j=0
Do Until rs.EOF=True
j=j+1
For i=0 to rs.Fields.Count
strTemp=strTemp & “” & vbcrlf
Next i
rs.MoveNext
Loop
rs.close
set rs=nothing
strTemp=strTemp & “” & vbcrlf & _
“” & vbcrlf & “” & vbcrlf & “EntryForm.submit” & vbcrlf & “” & vbcrlf & “”
f=FreeFile
Open “C:TempHTML.htm” for Binary Access Write As f
Put f,,strTemp
Close f
End Function

Now you have to make an ASP page that will accept this data:

<%
Dim cnn
Dim rs
Dim i
Dim j
set cnn=server.createobject("ADODB.Connection")
set rs=server.createobject("ADODB.Recordset")
cnn.Provider="Microsoft.Jet.OLEDB.4.0"
cnn.Open "D:MyDB.mdb"
rs.Open "tblMyDataTable",cnn,1,3
For i=1 to request.form("RecordCount")
rs.AddNew
For j=0 to rs.Fields.Count
rs.Fields(j).value=request.form(rs.Fields(j).Name & i)
Next
rs.Update
Next
rs.close
set rs=nothing
cnn.close
set cnn=nothing
response.write "” & vbcrlf & “window.close” & vbcrlf & “” & vbcrlf
%>

Then all you have to do, is launch the page in a browser, and it’ll do the rest.

Reply | Quote

Excellent idea, Drew.
Haven’t tried it out, but spotted a small typo: you should limit your loops to rs.Fields.Count – 1

Reply | Quote

Drew,
Thanks for the code. Claus I caught that mistake after the first run.
Drew I’m getting a page cannot be displaye in the browser(see pix).
The TempHTML.htm seems to be created ok.
I checked the table in the target db and all the fields are the same.
I caught the missing -1
What else can I check. I’m justarting to learn asp so bear with me.
Thanks,
Scott

Reply | Quote

Oops,
I didn’t add a -1 to the loop in the asp page.
Thanks again it works great.
ps. Is there a way to keep the message about closing the browser from coming up?
Scott

Reply | Quote

That’s browser thing. http://forums.devshed.com/archive/1/2003/7/1/65501%5B/url%5D goes into it a bit, with a possible work around.

However, if you are launching the site with a custom browser, something you control as it is, then you can simply watch for the completion of the .asp page, and close the browser from the outside (like if you are using Automation with IE, or if you are using a webbrowser control from VB).

Reply | Quote

That’s browser thing. http://forums.devshed.com/archive/1/2003/7/1/65501%5B/url%5D goes into it a bit, with a possible work around.

However, if you are launching the site with a custom browser, something you control as it is, then you can simply watch for the completion of the .asp page, and close the browser from the outside (like if you are using Automation with IE, or if you are using a webbrowser control from VB).

Reply | Quote

Oops,
I didn’t add a -1 to the loop in the asp page.
Thanks again it works great.
ps. Is there a way to keep the message about closing the browser from coming up?
Scott

Reply | Quote

Drew,
Thanks for the code. Claus I caught that mistake after the first run.
Drew I’m getting a page cannot be displaye in the browser(see pix).
The TempHTML.htm seems to be created ok.
I checked the table in the target db and all the fields are the same.
I caught the missing -1
What else can I check. I’m justarting to learn asp so bear with me.
Thanks,
Scott

Reply | Quote

Thanks for catching that. It’s a pain writing code in a post. Especially if you want to make it ‘look’ nice…having to put tab tags in, etc. Maybe once there is a search engine, we could start working on a VBE for the lounge, to post code too! .

Reply | Quote

Thanks for catching that. It’s a pain writing code in a post. Especially if you want to make it ‘look’ nice…having to put tab tags in, etc. Maybe once there is a search engine, we could start working on a VBE for the lounge, to post code too! .

Reply | Quote

Excellent idea, Drew.
Haven’t tried it out, but spotted a small typo: you should limit your loops to rs.Fields.Count – 1

Reply | Quote

Make a function that creates an HTML page with a form that posts the data that you want…

Function CreateHTMLForm()
Dim strTemp As String
Dim f As Long
Dim rs As Recordset
Dim strSQL as String
dim i as Long
Dim j as Long
strSQL=”SELECT * FROM MyTable”
set rs=CurrentDB.OpenRecordset(strSQL)
rs.MoveFirst
strTemp=”” & vbcrlf
j=0
Do Until rs.EOF=True
j=j+1
For i=0 to rs.Fields.Count
strTemp=strTemp & “” & vbcrlf
Next i
rs.MoveNext
Loop
rs.close
set rs=nothing
strTemp=strTemp & “” & vbcrlf & _
“” & vbcrlf & “” & vbcrlf & “EntryForm.submit” & vbcrlf & “” & vbcrlf & “”
f=FreeFile
Open “C:TempHTML.htm” for Binary Access Write As f
Put f,,strTemp
Close f
End Function

Now you have to make an ASP page that will accept this data:

<%
Dim cnn
Dim rs
Dim i
Dim j
set cnn=server.createobject("ADODB.Connection")
set rs=server.createobject("ADODB.Recordset")
cnn.Provider="Microsoft.Jet.OLEDB.4.0"
cnn.Open "D:MyDB.mdb"
rs.Open "tblMyDataTable",cnn,1,3
For i=1 to request.form("RecordCount")
rs.AddNew
For j=0 to rs.Fields.Count
rs.Fields(j).value=request.form(rs.Fields(j).Name & i)
Next
rs.Update
Next
rs.close
set rs=nothing
cnn.close
set cnn=nothing
response.write "” & vbcrlf & “window.close” & vbcrlf & “” & vbcrlf
%>

Then all you have to do, is launch the page in a browser, and it’ll do the rest.

Reply | Quote

Drew,
I have control of the intranet server. The amount of data will vary depending on the user that connects. A bunch? Shouldn’t be more than 100 records at a time. Unfortunately ther are 86 fields in the table(not my design).You mention POST through a form. Is that in access or a web based form?
With the table info I gave you, let me know the best way.
Thanks

Reply | Quote

I am assuming that you have the ability to create an asp page on the Intranet in question? The next question is are we talking about one record, or are we talking about a bunch of records. I ask, because if you are just pushing one record, and the data isn’t going to exceed a thousand (and change) characters, then you can just ‘open’ a link with the data in a querystring. But if you are pushing lots of records, you’ll need to ‘POST’ the data, through a form.

Reply | Quote