Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  2. #2
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  4. #4
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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="<HTML><FORM name=""EntryForm"" method=post action=""http://intranetsite.com/inputasppage.asp"">" & vbcrlf
    j=0
    Do Until rs.EOF=True
    j=j+1
    For i=0 to rs.Fields.Count
    strTemp=strTemp & "<input type=""hidden"" name=""" & rs.Fields(i).Name & j & """ value=""" & rs.Fields(i).Value & """>" & vbcrlf
    Next i
    rs.MoveNext
    Loop
    rs.close
    set rs=nothing
    strTemp=strTemp & "<input type=""hidden"" name=""RecordCount"" value=""" & j & """>" & vbcrlf & _
    "</form>" & vbcrlf & "<script language=""VBScript"">" & vbcrlf & "EntryForm.submit" & vbcrlf & "</script>" & vbcrlf & "</html>"
    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:

    < HTML >
    < HEAD >
    <%
    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 "<script language=""VBScript"">" & vbcrlf & "window.close" & vbcrlf & "</script>" & vbcrlf
    %>
    < /head >< /html >

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

  5. #5
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  6. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    Attached Images Attached Images

  7. #7
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  8. #8
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>.

  9. #9
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    That's browser thing. http://forums.devshed.com/archive/1/2003/7/1/65501 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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •