Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel UserForm to Collect Data for Access
I am trying to come up with a way of linking to an Access DB, via an Excel
interface, such as a UserForm, because many people in my firm do not have Access installed on their workstations. Im sure this is possible, but I dont know the best way to approach the problem. Im thinking of posting the Excel UserForm on our firms Intranet (WAN or LAN), and then ask users to open the UserForm, input some basic information, and then save the changes and so on and so forth. Is there some way to do all of this on a LAN or a WAN? Could the Excel file reside on a public network drive, and automatically download to am Access DB every time there is a chance (i.e., each time a user inputs something into the UserForm)? I would appreciate any and all help with this project. Regards, Ryan--- -- RyGuy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel UserForm to Collect Data for Access
Hello, I'll attempt to help by throwing some snippits of code your way.
Let's say you have a Userform, and on that userform you have a txtBox called txtBoxName, you also have a button named btnSendToDB You have a database that holds one table called names and within this tabel is a single field called name So, basically we want the user to input their name and click on the button and it will update the database with this new record. Private Sub cmdBtnSendToDB_Click() Dim usrName As String Dim dbConnection As ADODB.Connection Dim recordSet As ADODB.recordSet Dim strSQL As String Set dbConnection = New ADODB.Connection Set recordSet = New ADODB.recordSet 'The variable that'll hold the data within txtBoxName usrName = Me.txtBoxName.Value strSQL = "INSERT INTO names [fldName] VALUES '" & usrName & "'" 'Make sure your path is correct or you'll get an Authentication error. dbConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" & ThisWorkbook.Path & "/database/db.mdb;" dbConnection.CursorLocation = adUseClient ' This will open up the recordSet, using the SQL statement and the dbConnection - Connection. recordSet.Open strSQL, dbConnection 'Close of the connection. dbConnection.Close Set dbConnection = Nothing End Sub I haven't tested but as long as your path is correct, this should work. Remember to add the correct Reference in "Tools - References" I've added the one called "Microsoft ActiveX Data Objects 2.8 Library" this may be slightly different, depending what version you have. Adding this enables all the ADODB things that you see being used here. Hope this helps a little. "ryguy7272" wrote: I am trying to come up with a way of linking to an Access DB, via an Excel interface, such as a UserForm, because many people in my firm do not have Access installed on their workstations. Im sure this is possible, but I dont know the best way to approach the problem. Im thinking of posting the Excel UserForm on our firms Intranet (WAN or LAN), and then ask users to open the UserForm, input some basic information, and then save the changes and so on and so forth. Is there some way to do all of this on a LAN or a WAN? Could the Excel file reside on a public network drive, and automatically download to am Access DB every time there is a chance (i.e., each time a user inputs something into the UserForm)? I would appreciate any and all help with this project. Regards, Ryan--- -- RyGuy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel UserForm to Collect Data for Access
Change where it says [fldName] to just [name] as otherwise it'd be looking
for a field named [fldName]. I just put fld in front because I find it better to name all things in this fashion (makes it easier to see what I'm looking at this way). "NateBuckley" wrote: Hello, I'll attempt to help by throwing some snippits of code your way. Let's say you have a Userform, and on that userform you have a txtBox called txtBoxName, you also have a button named btnSendToDB You have a database that holds one table called names and within this tabel is a single field called name So, basically we want the user to input their name and click on the button and it will update the database with this new record. Private Sub cmdBtnSendToDB_Click() Dim usrName As String Dim dbConnection As ADODB.Connection Dim recordSet As ADODB.recordSet Dim strSQL As String Set dbConnection = New ADODB.Connection Set recordSet = New ADODB.recordSet 'The variable that'll hold the data within txtBoxName usrName = Me.txtBoxName.Value strSQL = "INSERT INTO names [fldName] VALUES '" & usrName & "'" 'Make sure your path is correct or you'll get an Authentication error. dbConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" & ThisWorkbook.Path & "/database/db.mdb;" dbConnection.CursorLocation = adUseClient ' This will open up the recordSet, using the SQL statement and the dbConnection - Connection. recordSet.Open strSQL, dbConnection 'Close of the connection. dbConnection.Close Set dbConnection = Nothing End Sub I haven't tested but as long as your path is correct, this should work. Remember to add the correct Reference in "Tools - References" I've added the one called "Microsoft ActiveX Data Objects 2.8 Library" this may be slightly different, depending what version you have. Adding this enables all the ADODB things that you see being used here. Hope this helps a little. "ryguy7272" wrote: I am trying to come up with a way of linking to an Access DB, via an Excel interface, such as a UserForm, because many people in my firm do not have Access installed on their workstations. Im sure this is possible, but I dont know the best way to approach the problem. Im thinking of posting the Excel UserForm on our firms Intranet (WAN or LAN), and then ask users to open the UserForm, input some basic information, and then save the changes and so on and so forth. Is there some way to do all of this on a LAN or a WAN? Could the Excel file reside on a public network drive, and automatically download to am Access DB every time there is a chance (i.e., each time a user inputs something into the UserForm)? I would appreciate any and all help with this project. Regards, Ryan--- -- RyGuy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel UserForm to Collect Data for Access
Wow!! Thanks Nate!! The code looks quite nice; elegant and efficient. I
made the changes that you recommended, and got this error: Run-time Error: Syntax error in INSERT INTO statement. Below is my code, behind UserForm1: Private Sub btnSendToDB_Click() Dim usrName As String Dim dbConnection As ADODB.Connection Dim recordSet As ADODB.recordSet Dim strSQL As String Set dbConnection = New ADODB.Connection Set recordSet = New ADODB.recordSet 'The variable that'll hold the data within txtBoxName usrName = Me.txtBoxName.Value strSQL = "INSERT INTO Names [Name] VALUES '" & usrName & "'" dbConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =C:\Documents and Settings\rms\Desktop\Access Models\ Reports.mdb" dbConnection.CursorLocation = adUseClient ' This will open up the recordSet, using the SQL statement and the dbConnection - Connection. recordSet.Open strSQL, dbConnection 'Close of the connection. dbConnection.Close Set dbConnection = Nothing End Sub The UserForm has a TextBox named €˜txtBoxName and a CommandButton named €˜btnSendToDB. The DB is named €˜Reports, it has several tTables, one of which is named €˜Names and in that Table, I have a Field named €˜Number which is of Data Type AutoNumber and I have a Field named €˜Name which is of Field Type Text. Do you have any idea what could be causing that INSERT INTO Error? Regards, Ryan--- -- RyGuy "NateBuckley" wrote: Change where it says [fldName] to just [name] as otherwise it'd be looking for a field named [fldName]. I just put fld in front because I find it better to name all things in this fashion (makes it easier to see what I'm looking at this way). "NateBuckley" wrote: Hello, I'll attempt to help by throwing some snippits of code your way. Let's say you have a Userform, and on that userform you have a txtBox called txtBoxName, you also have a button named btnSendToDB You have a database that holds one table called names and within this tabel is a single field called name So, basically we want the user to input their name and click on the button and it will update the database with this new record. Private Sub cmdBtnSendToDB_Click() Dim usrName As String Dim dbConnection As ADODB.Connection Dim recordSet As ADODB.recordSet Dim strSQL As String Set dbConnection = New ADODB.Connection Set recordSet = New ADODB.recordSet 'The variable that'll hold the data within txtBoxName usrName = Me.txtBoxName.Value strSQL = "INSERT INTO names [fldName] VALUES '" & usrName & "'" 'Make sure your path is correct or you'll get an Authentication error. dbConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" & ThisWorkbook.Path & "/database/db.mdb;" dbConnection.CursorLocation = adUseClient ' This will open up the recordSet, using the SQL statement and the dbConnection - Connection. recordSet.Open strSQL, dbConnection 'Close of the connection. dbConnection.Close Set dbConnection = Nothing End Sub I haven't tested but as long as your path is correct, this should work. Remember to add the correct Reference in "Tools - References" I've added the one called "Microsoft ActiveX Data Objects 2.8 Library" this may be slightly different, depending what version you have. Adding this enables all the ADODB things that you see being used here. Hope this helps a little. "ryguy7272" wrote: I am trying to come up with a way of linking to an Access DB, via an Excel interface, such as a UserForm, because many people in my firm do not have Access installed on their workstations. Im sure this is possible, but I dont know the best way to approach the problem. Im thinking of posting the Excel UserForm on our firms Intranet (WAN or LAN), and then ask users to open the UserForm, input some basic information, and then save the changes and so on and so forth. Is there some way to do all of this on a LAN or a WAN? Could the Excel file reside on a public network drive, and automatically download to am Access DB every time there is a chance (i.e., each time a user inputs something into the UserForm)? I would appreciate any and all help with this project. Regards, Ryan--- -- RyGuy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel UserForm to Collect Data for Access
On Tue, 27 May 2008 22:33:00 -0700, ryguy7272
wrote: I am trying to come up with a way of linking to an Access DB, via an Excel interface, such as a UserForm, because many people in my firm do not have Access installed on their workstations. I’m sure this is possible, but I don’t know the best way to approach the problem. I’m thinking of posting the Excel UserForm on our firm’s Intranet (WAN or LAN), and then ask users to open the UserForm, input some basic information, and then save the changes and so on and so forth. Is there some way to do all of this on a LAN or a WAN? Could the Excel file reside on a public network drive, and automatically download to am Access DB every time there is a chance (i.e., each time a user inputs something into the UserForm)? I would appreciate any and all help with this project. Here's a tutrorial that will help get you started, but it doesn't write back to the database. http://www.dailydoseofexcel.com/arch...erform-part-i/ -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel UserForm to Collect Data for Access
I just got it (from the Access Programming DG):
Private Sub btnSendToDB_Click() Dim usrName As String Dim dbConnection As ADODB.Connection Dim recordSet As ADODB.recordSet Dim strSQL As String Set dbConnection = New ADODB.Connection Set recordSet = New ADODB.recordSet 'The variable that'll hold the data within txtBoxName usrClient = Me.txtBoxName.Value strSQL = "INSERT INTO tblClients ([Clients]) VALUES ('" & usrClient & "')" dbConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =C:\Documents and Settings\rshuell\Desktop\Access Models\Diego_Reports.mdb" dbConnection.CursorLocation = adUseClient ' This will open up the recordSet, using the SQL statement and the dbConnection - Connection. recordSet.Open strSQL, dbConnection 'Close of the connection. dbConnection.Close Set dbConnection = Nothing End Sub Hope this helps others....... Regards, Ryan--- -- RyGuy "Dick Kusleika" wrote: On Tue, 27 May 2008 22:33:00 -0700, ryguy7272 wrote: I am trying to come up with a way of linking to an Access DB, via an Excel interface, such as a UserForm, because many people in my firm do not have Access installed on their workstations. Im sure this is possible, but I dont know the best way to approach the problem. Im thinking of posting the Excel UserForm on our firms Intranet (WAN or LAN), and then ask users to open the UserForm, input some basic information, and then save the changes and so on and so forth. Is there some way to do all of this on a LAN or a WAN? Could the Excel file reside on a public network drive, and automatically download to am Access DB every time there is a chance (i.e., each time a user inputs something into the UserForm)? I would appreciate any and all help with this project. Here's a tutrorial that will help get you started, but it doesn't write back to the database. http://www.dailydoseofexcel.com/arch...erform-part-i/ -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
collect data from one excel sheet to another | Excel Discussion (Misc queries) | |||
collect data from one excel sheet to another | Excel Worksheet Functions | |||
Collect data from Excel files | Excel Programming | |||
Userform - need to access data from a particular sheet | Excel Programming | |||
using a worksheet to collect data for access import | Excel Programming |