none
How to Save Records from any ComboBox on a Form to an Access Table Hosted on SharePoint? RRS feed

  • Question

  • I can easily save records from any ComboBox to a table in Access using VBA. Here's a great example.

    Private Sub AlternateSave_Click()
    
    Dim rst As Recordset
    Set rst = CurrentDb.OpenRecordset("tblRiskCard")
    With rst
         .AddNew
         .Fields("Risk") = ComboRisk.Value
         .Fields("Entity") = ComboBusiness.Value
         .Fields("WorkshopSegment") = ComboWorkshop.Value
         .Fields("PrimaryProduct") = ComboProduct.Value
         .Update
    End With
    
    MsgBox ("Records Saved!!")
    
    End Sub

    However, I need to do this for an Access database hosted on a SharePoint site and AFAIK, SharePoint doesn't support any VBA scripts. I tried to setup a simple Action-SaveRecord Macro to do the work, and that did nothing at all.

    Is SharePoint capable of doing this, or is this beyond the limits of the technology? I'm using SharePoint 2010 and Access 2010.

    Thanks!


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Saturday, February 27, 2016 12:20 AM

Answers

  • Hi. When you add SharePoint to the mix, there are multiple scenarios. If you have an Access frontend on your desktop with linked Lists to the SharePoint site, then you can use VBA to do what you want. However, if you're saying you have an Access database stored in a SharePoint Library as a file, then you must download a copy of the file to your desktop first to run the routine and then replace the same file in the Library. Or, if you're saying you have an Access Web Database where you use your Internet Browser to select from a combobox and you want to store the selection in a SharePoint List, then you can use web macros. So, which scenario do you actually have?
    • Marked as answer by ryguy72 Tuesday, March 1, 2016 6:24 PM
    Saturday, February 27, 2016 3:26 PM
  • Hi. The problem, I think, is you're trying to think of SharePoint as an Access database. The main difference between the two is Access is a desktop application (file system) while SharePoint uses web technology (client/server). If you want to manipulate data using SharePoint, you'll have to "throw away" the previous Access method. I put "throw away" in quotes because it's not really 100% accurate because you can use a "hybrid" solution. However, the main point I'm trying to make is we need to distinguish the Access way of doing things from the SharePoint way of doing things. Also, Access and SharePoint are used for two different purposes, so saying SharePoint is very limited as compared to Access is not really fair. If someone coming in from SharePoint tries to do what SharePoint can do using Access, they might also say Access is very limited, which we both know is not true.

    Okay, sorry to go on a rant, but I just wanted to clear the air and make sure we both understand we're talking about two different technologies.

    So, if you want to share data with 400 users all over the place using SharePoint, one way to do it is to create (or convert your tables into) SharePoint Lists to store your data. Basically, what I'm saying is take your data out of Access and put them in SharePoint. Once you do that, any user who has access to SharePoint can access your data. If you put your data in SharePoint Lists, SharePoint automatically creates View and Edit web forms for you, so your users simply use their web browser to see and change the data. All uses immediately see the changes made by other users.

    You can also keep your Access front end and link to the SharePoint Lists, so you can manage the data on your desktop, and you can keep using VBA.

    In SharePoint, you can use Workflows to make it do certain things, like you would using VBA on a desktop application.

    So, the real question becomes, are you willing to move your data to SharePoint?

    • Marked as answer by ryguy72 Tuesday, March 1, 2016 6:24 PM
    Sunday, February 28, 2016 5:13 PM
  • I can do this all in ASP.NET, if that's the way to go.  I already tested a C# script, which works fine.  That's a completely different methodology from SharePoint.  I'm just trying to figure out the best practice here.


    Just a quick question because I'm curious... Was the ASP.Net C# script you tested executed on the desktop or the web server? Thanks.
    • Marked as answer by ryguy72 Tuesday, March 1, 2016 6:24 PM
    Sunday, February 28, 2016 5:30 PM
  • Hi. Thanks for the clarification on the C# script. As I suspected, you were testing it on your desktop. So, even if you "load" it on the server, what do you expect will happen? Where do you plan on storing the Access file? Will it be on the server too? If so, then yes, it's a valid option.

    As for using SharePoint though, you didn't answer my question. Are you against moving the data out of Access and placing in into SharePoint? If you move the data to SharePoint, it would eliminate the need for the user to download the Access file, make the changes to the data or add new ones and then upload it back to SharePoint. Without this process in the mix, you can avoid all those concerns you have about "data collisions."

    So, if you are willing to "move" the data to SharePoint, then you have two choices for your users. (1) You can let them use their web browsers to add new data, or (2) Give them a copy of the Access front end (with VBA), which they'll keep on their desktop, and they can just open it. To them, it would seem like they are working with the data locally when it's actually in the "cloud" (SharePoint Lists).

    Hope it helps...

    • Marked as answer by ryguy72 Tuesday, March 1, 2016 6:24 PM
    Sunday, February 28, 2016 7:58 PM
  • Hi. Are you using an on-premise SharePoint farm or are you using O365? Are you able to give me access to your SharePoint site, so I can make you a demo?

    Re: SharePoint features. Yes, it's very good at storing files/documents and have version control, but it can do much more than that (aside from what you're trying to do, which it can totally do). It can also create a blog, wiki, discussion forum, surveys, image gallery, slide (PowerPoint) gallery, RSS feeds, and many more...

    Re: users adding/changing data at the same time. SharePoint will commit the changes on a first-come-first-serve basis. If two users try to change the same data at the same time, one of them will get an error or notice as soon as they try to save it.

    Hope this helps...

    • Marked as answer by ryguy72 Tuesday, March 1, 2016 6:24 PM
    Sunday, February 28, 2016 8:42 PM
  • Oh, by the way, regarding the C#/.NET approach of placing the database file on the server. Yes, it would work because the script will be able to locate the file on the server and connect to it.

    The good thing about SharePoint (or the difference with it) is you don't store the database file on the server. Or more accurately, you don't use a "file" at all. The data in your database tables will now reside inside SharePoint in Lists. From there, SharePoint can access them, and multiple users can work with the data at the same time from anywhere they have access to the Internet.

    Hope that makes sense...

    • Marked as answer by ryguy72 Tuesday, March 1, 2016 6:24 PM
    Sunday, February 28, 2016 8:45 PM
  • Hi. Okay, I'll see if I can find a link for you. In the meantime, try the following steps:

    1. Open your Access database with the tables containing the data you are already using.

    2. On the Ribbon click on the Database Tools tab,

    3. In the Move Data group, click on the SharePoint button

    4. Follow the Wizard prompts

    Make sure you back up your database file first before trying the above.

    • Marked as answer by ryguy72 Tuesday, March 1, 2016 6:24 PM
    Sunday, February 28, 2016 9:42 PM
  • Hi. I'm not in front of a computer now, so I'll be brief and elaborate more later.

    The SharePoint List should have as many columns as you have fields in the table you exported to SharePoint. Did you change your table structure before using the migration wizard?

    Again, it's important to keep the distinction between how things are done in Access and in SharePoint. Now that the data has been moved to SharePoint Lists, changes made to them won't show up in the original Access tables anymore. In the same token,if you link to these Lists from Access, changes to the data still go to the SharePoint Lists.

    As I said though, I'll explain more later.

    Sent from phone...


    • Edited by .theDBguy Monday, February 29, 2016 12:05 AM
    • Marked as answer by ryguy72 Tuesday, March 1, 2016 6:24 PM
    Monday, February 29, 2016 12:03 AM

All replies

  • Hi. When you add SharePoint to the mix, there are multiple scenarios. If you have an Access frontend on your desktop with linked Lists to the SharePoint site, then you can use VBA to do what you want. However, if you're saying you have an Access database stored in a SharePoint Library as a file, then you must download a copy of the file to your desktop first to run the routine and then replace the same file in the Library. Or, if you're saying you have an Access Web Database where you use your Internet Browser to select from a combobox and you want to store the selection in a SharePoint List, then you can use web macros. So, which scenario do you actually have?
    • Marked as answer by ryguy72 Tuesday, March 1, 2016 6:24 PM
    Saturday, February 27, 2016 3:26 PM
  • I would expect the Access DB will be on SharePoint 100% of the time.  The users are not really sophisticated, so I can't expect them to download the database, run VBA to perform a task, and then upload the database.  I really want them to interact with the database in SharePoint ONLY.  After about 400 users enter data, a dedicated IT person can dump the data into an Excel file and send it back to the users.  That's how I envision this running.  

    So, the VBA script that I posted works fine on a desktop, but not in SharePoint.  I'm fine with that.  Is there a way to get a Macro (not VBA) to send selected options from several ComboBoxes, to an Access Table?  That's what I'm trying to figure out.  it seems like SharePoint is very limited in it's capabilities, other than acting as a file repository, which offers some basic mouse-click functionality. 

    I can do this all in ASP.NET, if that's the way to go.  I already tested a C# script, which works fine.  That's a completely different methodology from SharePoint.  I'm just trying to figure out the best practice here.

    #1)  I want to set this up for non-sophisticated to interact with and ensure it works fine.

    #2)  I want to hand this off to an IP expert and have him/her do minimal work (dump results from Access into Excel and send it to the users).  I can't support this after the project is done.

    That's it!!


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Sunday, February 28, 2016 2:46 PM
  • Hi. The problem, I think, is you're trying to think of SharePoint as an Access database. The main difference between the two is Access is a desktop application (file system) while SharePoint uses web technology (client/server). If you want to manipulate data using SharePoint, you'll have to "throw away" the previous Access method. I put "throw away" in quotes because it's not really 100% accurate because you can use a "hybrid" solution. However, the main point I'm trying to make is we need to distinguish the Access way of doing things from the SharePoint way of doing things. Also, Access and SharePoint are used for two different purposes, so saying SharePoint is very limited as compared to Access is not really fair. If someone coming in from SharePoint tries to do what SharePoint can do using Access, they might also say Access is very limited, which we both know is not true.

    Okay, sorry to go on a rant, but I just wanted to clear the air and make sure we both understand we're talking about two different technologies.

    So, if you want to share data with 400 users all over the place using SharePoint, one way to do it is to create (or convert your tables into) SharePoint Lists to store your data. Basically, what I'm saying is take your data out of Access and put them in SharePoint. Once you do that, any user who has access to SharePoint can access your data. If you put your data in SharePoint Lists, SharePoint automatically creates View and Edit web forms for you, so your users simply use their web browser to see and change the data. All uses immediately see the changes made by other users.

    You can also keep your Access front end and link to the SharePoint Lists, so you can manage the data on your desktop, and you can keep using VBA.

    In SharePoint, you can use Workflows to make it do certain things, like you would using VBA on a desktop application.

    So, the real question becomes, are you willing to move your data to SharePoint?

    • Marked as answer by ryguy72 Tuesday, March 1, 2016 6:24 PM
    Sunday, February 28, 2016 5:13 PM
  • I can do this all in ASP.NET, if that's the way to go.  I already tested a C# script, which works fine.  That's a completely different methodology from SharePoint.  I'm just trying to figure out the best practice here.


    Just a quick question because I'm curious... Was the ASP.Net C# script you tested executed on the desktop or the web server? Thanks.
    • Marked as answer by ryguy72 Tuesday, March 1, 2016 6:24 PM
    Sunday, February 28, 2016 5:30 PM
  • The C# script was tested on the desktop, but I would load it onto a server for the actual production.  Here's the design view.

    Here's the code.

    using System;
    using System.Data;
    using System.Data.OleDb;
    using System.Web.UI;
    using System.Windows.Forms;
    
    namespace WebApplication4
    {
        public partial class WebForm1 : System.Web.UI.Page
        {
            private object n;
    
            public object MessageBox { get; private set; }
            public object MassageBox { get; private set; }
            public object MessageBoxIcon { get; private set; }
            public object MessageBoxButton { get; private set; }
    
            protected void Page_Load(object sender, EventArgs e)
            {
    
            }
    
            protected void Button1_Click(object sender, EventArgs e)
            {
    
                OleDbConnection conn = new OleDbConnection();
                conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\path_here\Risk_DB.mdb";
    
                string rc_name = TextBox1.Text.Trim();
                string rc_LoB = TextBox2.Text.Trim();
                string rc_RBP = TextBox3.Text.Trim();
                OleDbCommand cmd = new OleDbCommand(@"INSERT INTO tbl_IHC_Risk_Card (rc_name, rc_LoB, rc_RBP) VALUES (@rc_name, @rc_LoB, @rc_RBP)")
                {
                    Connection = conn
                };
    
                conn.Open();
    
                if (conn.State == ConnectionState.Open)
                {
                    // you should always use parameterized queries to avoid SQL Injection
                    cmd.Parameters.Add("@rc_name", OleDbType.VarChar).Value = rc_name;
                    cmd.Parameters.Add("@rc_LoB", OleDbType.VarChar).Value = rc_LoB;
                    cmd.Parameters.Add("@rc_RBP", OleDbType.VarChar).Value = rc_RBP;
    
                    try
                    {
                        cmd.ExecuteNonQuery();
                        Type cstype = this.GetType();
    
                        // Get a ClientScriptManager reference from the Page class.
                        ClientScriptManager cs = Page.ClientScript;
    
                        // Check to see if the startup script is already registered.
                        if (!cs.IsStartupScriptRegistered(cstype, "PopupScript"))
                        {
                            String cstext = "alert('Data Added to Database!!');";
                            cs.RegisterStartupScript(cstype, "PopupScript", cstext, true);
                        }
    
                        conn.Close();
                    }
                    catch (OleDbException ex)
                    {
    
                        Type cstype = this.GetType();
                        ClientScriptManager cs = Page.ClientScript;
    
                        // Check to see if the startup script is already registered.
                        if (!cs.IsStartupScriptRegistered(cstype, "PopupScript"))
                        {
                            String cstext = "alert('There was an issue writing to the database!  Please check your connection string!!')";
                            cs.RegisterStartupScript(cstype, "PopupScript", cstext, true);
                        }
                        conn.Close();
                    }
                }
                else
                {
    
                }
    
            }
    
        }
    }

    All I was doing is testing the functionality to make sure it works, and it's fine.  If I go this way, I'll enhance the code to do what I need it to do.  Honestly, i think this is overkill.  As I read more about SharePoint, I'm thinking the best thing to do is load the database with the VBA and have users do the following.

    #1)  Export database to desktop.

    #2)  Choose items from multiple ComboBoxes (bound to Table1), Run VBA script, and save data (write to Table2).

    #3)  Re-upload the exported database to SharePoint.

    #4)  Done.

    Do you think this is the best practice?  I'm just looking for the easiest way to help non-sophisticated users manage a process that is somewhat sophisticated.

    I'll do some research on SharePoint Lists, as you mentioned.  Maybe that's an option.  I definitely want things to be synced.  I guess the only thing I'm concerned about is having people export the database (to each user's desktop) and then having people re-upload the database and then there are concurrency issues, or perhaps someone uploading the database and wiping out someone else's changes.  That would be a disaster!!

    Any additional feedback would be great.  I'm spending all day (Sunday) researching this so hopefully tomorrow (Monday) I can start doing the work on it.

    Thanks again.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.


    • Edited by ryguy72 Sunday, February 28, 2016 6:00 PM
    Sunday, February 28, 2016 5:58 PM
  • Hi. Thanks for the clarification on the C# script. As I suspected, you were testing it on your desktop. So, even if you "load" it on the server, what do you expect will happen? Where do you plan on storing the Access file? Will it be on the server too? If so, then yes, it's a valid option.

    As for using SharePoint though, you didn't answer my question. Are you against moving the data out of Access and placing in into SharePoint? If you move the data to SharePoint, it would eliminate the need for the user to download the Access file, make the changes to the data or add new ones and then upload it back to SharePoint. Without this process in the mix, you can avoid all those concerns you have about "data collisions."

    So, if you are willing to "move" the data to SharePoint, then you have two choices for your users. (1) You can let them use their web browsers to add new data, or (2) Give them a copy of the Access front end (with VBA), which they'll keep on their desktop, and they can just open it. To them, it would seem like they are working with the data locally when it's actually in the "cloud" (SharePoint Lists).

    Hope it helps...

    • Marked as answer by ryguy72 Tuesday, March 1, 2016 6:24 PM
    Sunday, February 28, 2016 7:58 PM
  • Yes, in the C# scenario, I would load the DB onto the server, of course.

    Back to SharePoint, you said . . .

    (1) You can let them use their web browsers to add new data.

    How do I do that?  I tried to create a few custom libraries, and lists, on SharePoint.  I added some columns, easy enough.  I clicked 'Choice' and '<label for="idChoices">Type each choice on a separate line</label>:' and entered several values in there.  Now, I can't see them anywhere.  I can't see the values or select them; it seems pretty useless.  When I try to add a new item, it just let's me select items from that list.  That makes no sense at all. 

    I'm rally starting to think SharePoint is not the right tool for allowing users to select data elements.  I think for storing documents and checking-in or checking-out documents, it's fine.  However, as an interactive environment for MANY users, I don't see how SharePoint is going to handle the demand.  I can't even get it to list a single vector of data elements.  Ugh!!!

    (2) Give them a copy of the Access front end (with VBA), which they'll keep on their desktop, and they can just open it. To them, it would seem like they are working with the data locally when it's actually in the "cloud".  This seems like the way to go.  This is the only thing that actually works for me.  But, then what happens if users write records to the Db at exactly the same time.  That, potentially, could be a fiasco!  Ugh!!

    This may be a task for .NET.  ASP & C# seem like the best option, all things considered.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.


    • Edited by ryguy72 Sunday, February 28, 2016 9:15 PM
    Sunday, February 28, 2016 8:31 PM
  • Hi. Are you using an on-premise SharePoint farm or are you using O365? Are you able to give me access to your SharePoint site, so I can make you a demo?

    Re: SharePoint features. Yes, it's very good at storing files/documents and have version control, but it can do much more than that (aside from what you're trying to do, which it can totally do). It can also create a blog, wiki, discussion forum, surveys, image gallery, slide (PowerPoint) gallery, RSS feeds, and many more...

    Re: users adding/changing data at the same time. SharePoint will commit the changes on a first-come-first-serve basis. If two users try to change the same data at the same time, one of them will get an error or notice as soon as they try to save it.

    Hope this helps...

    • Marked as answer by ryguy72 Tuesday, March 1, 2016 6:24 PM
    Sunday, February 28, 2016 8:42 PM
  • Oh, by the way, regarding the C#/.NET approach of placing the database file on the server. Yes, it would work because the script will be able to locate the file on the server and connect to it.

    The good thing about SharePoint (or the difference with it) is you don't store the database file on the server. Or more accurately, you don't use a "file" at all. The data in your database tables will now reside inside SharePoint in Lists. From there, SharePoint can access them, and multiple users can work with the data at the same time from anywhere they have access to the Internet.

    Hope that makes sense...

    • Marked as answer by ryguy72 Tuesday, March 1, 2016 6:24 PM
    Sunday, February 28, 2016 8:45 PM
  • No, I don't have O365.  Maybe that's easier, I don't know.  I've never used it before.  I'm using Office 2010 and SharePoint 2010.  I can get SharePoint 2013 easy enough, if that's better.  In terms of MS Office, I don't know for sure what all users will have.  Different people may have different things like Office 2007, 2010, or 2013.  Im pretty sure any of those 3 are fine.

    Can you post a link that describes EXACTLY how to setup and use Lists with Access?  Today I'm reading a few books about SharePoint and Googling for some related resources.  I still haven't come across a really good tutorial that describes how to link a SharePoint List to an Access table.  Even the basic lists that I've setup don't really do anything at all.  They certainly don't display any data, like I would expect any list to do.  Maybe I'm approaching it completely wrong.  I don't know.  These lists don't do anything at all, as far as I can tell.

    Thanks again.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Sunday, February 28, 2016 9:27 PM
  • Hi. Okay, I'll see if I can find a link for you. In the meantime, try the following steps:

    1. Open your Access database with the tables containing the data you are already using.

    2. On the Ribbon click on the Database Tools tab,

    3. In the Move Data group, click on the SharePoint button

    4. Follow the Wizard prompts

    Make sure you back up your database file first before trying the above.

    • Marked as answer by ryguy72 Tuesday, March 1, 2016 6:24 PM
    Sunday, February 28, 2016 9:42 PM
  • That actually does look better now! 

    Before, I had several fields in one table; now I have one field per table, and this translates to several lists in SharePoint.  I guess this is the way to go with it.  Now, how do I actually use those lists to do something useful?  I'd like to see several ComboBox objects on a form, so a user can select an item from each ComboBox, and then when each selection is made, run a Macro (non-VBA) and enter those records in a Access table in SharePoint?  That's really all I need to do!

    Thanks again.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Sunday, February 28, 2016 10:57 PM
  • Hi. I'm not in front of a computer now, so I'll be brief and elaborate more later.

    The SharePoint List should have as many columns as you have fields in the table you exported to SharePoint. Did you change your table structure before using the migration wizard?

    Again, it's important to keep the distinction between how things are done in Access and in SharePoint. Now that the data has been moved to SharePoint Lists, changes made to them won't show up in the original Access tables anymore. In the same token,if you link to these Lists from Access, changes to the data still go to the SharePoint Lists.

    As I said though, I'll explain more later.

    Sent from phone...


    • Edited by .theDBguy Monday, February 29, 2016 12:05 AM
    • Marked as answer by ryguy72 Tuesday, March 1, 2016 6:24 PM
    Monday, February 29, 2016 12:03 AM