Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    manissinha is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    21

    Need help to save record from excel vba userform to Access database

    Hi

    i have create a userform in excel for multiple user to use all at a time and i want to save its input record into Access database. i am able to save it on excel but if multiple user user at same time to save record it giving error , that's why i want to use Access ,
    please help to to save record into Access database

    Thanks


    MAnis

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Why don't you build the form in Access?

    You already have a split database by storing data in Access (the backend) with Excel as the user interface (frontend). Just build another Access file as the frontend interface. Set links to the backend tables. Each user runs their own copy of the frontend.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    manissinha is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    21
    hi
    for all user Access not installed on their system, so i create in excel

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Install Access Runtime for those users. It is freebie.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    manissinha is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    21
    due to some reason can't do this

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    manissinha is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    21
    Still not able to do .... please help

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    What did you try and what happened - error message, wrong results, nothing?

    Did you try having each user run their own copy of the Excel?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I finally tried downloading your file but get error message the zip file is invalid.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    manissinha is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    21
    please find the attachment
    Attached Files Attached Files

  12. #12
    manissinha is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    21
    Hi

    now i can add record in accesee data base using below code

    Private Sub cmbAddRecord_Click()
    Dim cn As New ADODB.Connection
    Dim cs As String
    Dim rs1 As New ADODB.Recordset
    cs = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Data2.mdb"
    cn.Open cs
    rs1.Open "Table1", cn, adOpenDynamic, adLockOptimistic

    With rs1
    .AddNew
    .Fields("Name") = Me.TextBox0
    .Fields("username") = Me.TextBox1
    .Fields("password") = Me.TextBox2
    .Fields("information") = Me.TextBox3
    .Update
    End With


    Set rs1 = Nothing
    cn.Close
    Set cn = Nothing
    TextBox0 = ""
    TextBox1 = ""
    TextBox2 = ""
    TextBox3 = ""
    End Sub


    but when i search record by using below code its giving the always first record from Access table\database

    Dim cn1 As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim searchstring As String
    ' connect to the Access database
    Set cn1 = New ADODB.Connection
    cn1.Open "Provider=Microsoft.Ace.OLEDB.12.0; " & _
    "Data Source=D:\Data2.mdb"
    ' open a recordset
    Set rs = New ADODB.Recordset
    Search = TextBox0.Value
    rs.Open "Table1", cn1, adOpenDynamic, adLockOptimistic


    searchstring = "SELECT Name, username,password,information FROM tblMsrTotals WHERE [Name] = '" & TextBox0.Value & "'"
    ' searchstring = "SELECT Name, FROM Table1 WHERE [Name] = TextBox0.text" & Search
    ' rs.Open searchstring, cn1, adOpenStatic

    TextBox0.Text = rs.Fields("Name") 'or whatever
    TextBox1.Text = rs.Fields("username") 'or whatever
    TextBox2.Text = rs.Fields("password") 'or whatever
    TextBox3.Text = rs.Fields("information") 'or whatever



    Set rs = Nothing

    End Sub


    please help me

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Don't see that code in the posted workbook.

    You are opening recordset from table so of course it will always read the first record.

    Should open recordset from filtered query, as shown in the commented rs.Open line.


    Name is a reserved word, should not use reserved words as names.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    manissinha is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    21
    not getting you

  15. #15
    manissinha is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    21
    hi june7

    not getting you please describe with code...

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 10-07-2016, 09:27 AM
  2. Exporting Attachments from a UserForm to Access Table
    By Baz16 in forum Import/Export Data
    Replies: 8
    Last Post: 06-30-2015, 05:03 AM
  3. Replies: 7
    Last Post: 09-11-2014, 12:26 PM
  4. Populate Word Userform with data from Access
    By edi dhar in forum Programming
    Replies: 5
    Last Post: 08-04-2014, 11:02 AM
  5. Replies: 5
    Last Post: 02-03-2014, 03:06 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums