Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
connect excel user form to access database
Can a password protected access database be connected to Excel user form? Excel user form is stored on shared drive and used by users to input data into access database. Please Help Thanks in Advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
connect excel user form to access database
Are you running this from Access? If so, read below: It is quite easy to perform operations in Excel, and control the entire process from Access. Make sure you set a reference to Excel, and then run this code in an Access module: Option Compare Database Option Explicit ' Use this to make sure your variables are defined ' One way to be able to use these objects throughout the Module is to Declare them here, and not in a Sub Private objExcel As Excel.Application Private xlWB As Excel.Workbook Private xlWS As Excel.Worksheet Sub Control() Dim strFile As String strFile = "C:\Documents and Settings\rshuell\Desktop\Ryan\Crosstab Query.xls" €˜Of course, this is just an example; put the actual path to your actual file here€¦ ' Opens Excel and makes it Visible Set objExcel = New Excel.Application objExcel.Visible = True ' Opens up a Workbook Set xlWB = objExcel.Workbooks.Open(strFile) ' Sets the Workseet to the last active sheet - Better to use the commented version and use the name of the sheet. Set xlWS = xlWB.ActiveSheet ' Set xlWS = xlWB("Sheet1") With xlWS ' You are now working with the Named file and the named worksheet ' Your Excel code begins here€¦you can even record a macro and make the process super easy!! End With ' Close and Cleanup xlWB.SaveAs xlSaveFile xlWB.Close xlapp.Quit Set xlapp = Nothing End Sub HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "sam" wrote: Can a password protected access database be connected to Excel user form? Excel user form is stored on shared drive and used by users to input data into access database. Please Help Thanks in Advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
connect excel user form to access database
Thanks a lot for you help, I am running it through excel I think. I have a user form designed in Excel (coded in Excel). Inputing data in excel form would populate access database. And the Access database is password protected. Is this what you are talking about? I hope i made it clear. Thanks in Advance "ryguy7272" wrote: Are you running this from Access? If so, read below: It is quite easy to perform operations in Excel, and control the entire process from Access. Make sure you set a reference to Excel, and then run this code in an Access module: Option Compare Database Option Explicit ' Use this to make sure your variables are defined ' One way to be able to use these objects throughout the Module is to Declare them here, and not in a Sub Private objExcel As Excel.Application Private xlWB As Excel.Workbook Private xlWS As Excel.Worksheet Sub Control() Dim strFile As String strFile = "C:\Documents and Settings\rshuell\Desktop\Ryan\Crosstab Query.xls" €˜Of course, this is just an example; put the actual path to your actual file here€¦ ' Opens Excel and makes it Visible Set objExcel = New Excel.Application objExcel.Visible = True ' Opens up a Workbook Set xlWB = objExcel.Workbooks.Open(strFile) ' Sets the Workseet to the last active sheet - Better to use the commented version and use the name of the sheet. Set xlWS = xlWB.ActiveSheet ' Set xlWS = xlWB("Sheet1") With xlWS ' You are now working with the Named file and the named worksheet ' Your Excel code begins here€¦you can even record a macro and make the process super easy!! End With ' Close and Cleanup xlWB.SaveAs xlSaveFile xlWB.Close xlapp.Quit Set xlapp = Nothing End Sub HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "sam" wrote: Can a password protected access database be connected to Excel user form? Excel user form is stored on shared drive and used by users to input data into access database. Please Help Thanks in Advance |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
connect excel user form to access database
Ok, going from Excel to Access! Check this out: http://www.erlandsendata.no/english/...badacexportado This too: http://www.erlandsendata.no/english/...badacexportdao As for the password, I'm not sure how that will work. I've used passwords on some projects, but if anyone serious about breaking in, that person already knows how to defeat the password. I found this on an old pose at this same DG: you may try this macro to know if a table exist Dim cnn As New ADODB.Connection Dim rsT As ADODB.Recordset Dim Verif As Boolean Dim dbName As String Set cnn = New Connection dbName = ("C:\Data\MYDataBase1.mdb") With cnn ..Provider = "Microsoft.Jet.OLEDB.4.0" ..Mode = adModeWrite ..Properties("Jet OLEDB:Database Password") = "abc" ..Open dbName End With Set rsT = cnn.OpenSchema(adSchemaTables) Verif = False While Not rsT.EOF If rsT.Fields("TABLE_NAME") = "MYTABLE" Then Verif = True rsT.MoveNext Wend If Verif = False Then MsgBox "The Table does not Exist ." Else MsgBox "the table exist" End If cnn.Close Set cnn = Nothing Set rsT = Nothing Can you work with that? HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "sam" wrote: Thanks a lot for you help, I am running it through excel I think. I have a user form designed in Excel (coded in Excel). Inputing data in excel form would populate access database. And the Access database is password protected. Is this what you are talking about? I hope i made it clear. Thanks in Advance "ryguy7272" wrote: Are you running this from Access? If so, read below: It is quite easy to perform operations in Excel, and control the entire process from Access. Make sure you set a reference to Excel, and then run this code in an Access module: Option Compare Database Option Explicit ' Use this to make sure your variables are defined ' One way to be able to use these objects throughout the Module is to Declare them here, and not in a Sub Private objExcel As Excel.Application Private xlWB As Excel.Workbook Private xlWS As Excel.Worksheet Sub Control() Dim strFile As String strFile = "C:\Documents and Settings\rshuell\Desktop\Ryan\Crosstab Query.xls" €˜Of course, this is just an example; put the actual path to your actual file here€¦ ' Opens Excel and makes it Visible Set objExcel = New Excel.Application objExcel.Visible = True ' Opens up a Workbook Set xlWB = objExcel.Workbooks.Open(strFile) ' Sets the Workseet to the last active sheet - Better to use the commented version and use the name of the sheet. Set xlWS = xlWB.ActiveSheet ' Set xlWS = xlWB("Sheet1") With xlWS ' You are now working with the Named file and the named worksheet ' Your Excel code begins here€¦you can even record a macro and make the process super easy!! End With ' Close and Cleanup xlWB.SaveAs xlSaveFile xlWB.Close xlapp.Quit Set xlapp = Nothing End Sub HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "sam" wrote: Can a password protected access database be connected to Excel user form? Excel user form is stored on shared drive and used by users to input data into access database. Please Help Thanks in Advance |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
connect excel user form to access database
Thanks for the help ryguy7272. I am using DAO for connecting my excel form to access DB. I think your code is for ADO Connection? Can you please tell if it can be used for DAO as well? Thank you in Advance. "ryguy7272" wrote: Ok, going from Excel to Access! Check this out: http://www.erlandsendata.no/english/...badacexportado This too: http://www.erlandsendata.no/english/...badacexportdao As for the password, I'm not sure how that will work. I've used passwords on some projects, but if anyone serious about breaking in, that person already knows how to defeat the password. I found this on an old pose at this same DG: you may try this macro to know if a table exist Dim cnn As New ADODB.Connection Dim rsT As ADODB.Recordset Dim Verif As Boolean Dim dbName As String Set cnn = New Connection dbName = ("C:\Data\MYDataBase1.mdb") With cnn .Provider = "Microsoft.Jet.OLEDB.4.0" .Mode = adModeWrite .Properties("Jet OLEDB:Database Password") = "abc" .Open dbName End With Set rsT = cnn.OpenSchema(adSchemaTables) Verif = False While Not rsT.EOF If rsT.Fields("TABLE_NAME") = "MYTABLE" Then Verif = True rsT.MoveNext Wend If Verif = False Then MsgBox "The Table does not Exist ." Else MsgBox "the table exist" End If cnn.Close Set cnn = Nothing Set rsT = Nothing Can you work with that? HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "sam" wrote: Thanks a lot for you help, I am running it through excel I think. I have a user form designed in Excel (coded in Excel). Inputing data in excel form would populate access database. And the Access database is password protected. Is this what you are talking about? I hope i made it clear. Thanks in Advance "ryguy7272" wrote: Are you running this from Access? If so, read below: It is quite easy to perform operations in Excel, and control the entire process from Access. Make sure you set a reference to Excel, and then run this code in an Access module: Option Compare Database Option Explicit ' Use this to make sure your variables are defined ' One way to be able to use these objects throughout the Module is to Declare them here, and not in a Sub Private objExcel As Excel.Application Private xlWB As Excel.Workbook Private xlWS As Excel.Worksheet Sub Control() Dim strFile As String strFile = "C:\Documents and Settings\rshuell\Desktop\Ryan\Crosstab Query.xls" €˜Of course, this is just an example; put the actual path to your actual file here€¦ ' Opens Excel and makes it Visible Set objExcel = New Excel.Application objExcel.Visible = True ' Opens up a Workbook Set xlWB = objExcel.Workbooks.Open(strFile) ' Sets the Workseet to the last active sheet - Better to use the commented version and use the name of the sheet. Set xlWS = xlWB.ActiveSheet ' Set xlWS = xlWB("Sheet1") With xlWS ' You are now working with the Named file and the named worksheet ' Your Excel code begins here€¦you can even record a macro and make the process super easy!! End With ' Close and Cleanup xlWB.SaveAs xlSaveFile xlWB.Close xlapp.Quit Set xlapp = Nothing End Sub HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "sam" wrote: Can a password protected access database be connected to Excel user form? Excel user form is stored on shared drive and used by users to input data into access database. Please Help Thanks in Advance |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
connect excel user form to access database
Did you look at the second link? Also, check this out: http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm The code there is quite amazing! I've used many of those import/export examples many times. Maybe the information there will give you a few other ideas. As the old saying goes, there is always more than one way to skin a cat. BTW, the code there runs in Access, but if you fiddle with it a bit, you may be able to get it working in Excel. If not, post back with what you have, and someone at the Excel Programming DG may be able to help you get it working to your specifications. HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "sam" wrote: Thanks for the help ryguy7272. I am using DAO for connecting my excel form to access DB. I think your code is for ADO Connection? Can you please tell if it can be used for DAO as well? Thank you in Advance. "ryguy7272" wrote: Ok, going from Excel to Access! Check this out: http://www.erlandsendata.no/english/...badacexportado This too: http://www.erlandsendata.no/english/...badacexportdao As for the password, I'm not sure how that will work. I've used passwords on some projects, but if anyone serious about breaking in, that person already knows how to defeat the password. I found this on an old pose at this same DG: you may try this macro to know if a table exist Dim cnn As New ADODB.Connection Dim rsT As ADODB.Recordset Dim Verif As Boolean Dim dbName As String Set cnn = New Connection dbName = ("C:\Data\MYDataBase1.mdb") With cnn .Provider = "Microsoft.Jet.OLEDB.4.0" .Mode = adModeWrite .Properties("Jet OLEDB:Database Password") = "abc" .Open dbName End With Set rsT = cnn.OpenSchema(adSchemaTables) Verif = False While Not rsT.EOF If rsT.Fields("TABLE_NAME") = "MYTABLE" Then Verif = True rsT.MoveNext Wend If Verif = False Then MsgBox "The Table does not Exist ." Else MsgBox "the table exist" End If cnn.Close Set cnn = Nothing Set rsT = Nothing Can you work with that? HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "sam" wrote: Thanks a lot for you help, I am running it through excel I think. I have a user form designed in Excel (coded in Excel). Inputing data in excel form would populate access database. And the Access database is password protected. Is this what you are talking about? I hope i made it clear. Thanks in Advance "ryguy7272" wrote: Are you running this from Access? If so, read below: It is quite easy to perform operations in Excel, and control the entire process from Access. Make sure you set a reference to Excel, and then run this code in an Access module: Option Compare Database Option Explicit ' Use this to make sure your variables are defined ' One way to be able to use these objects throughout the Module is to Declare them here, and not in a Sub Private objExcel As Excel.Application Private xlWB As Excel.Workbook Private xlWS As Excel.Worksheet Sub Control() Dim strFile As String strFile = "C:\Documents and Settings\rshuell\Desktop\Ryan\Crosstab Query.xls" €˜Of course, this is just an example; put the actual path to your actual file here€¦ ' Opens Excel and makes it Visible Set objExcel = New Excel.Application objExcel.Visible = True ' Opens up a Workbook Set xlWB = objExcel.Workbooks.Open(strFile) ' Sets the Workseet to the last active sheet - Better to use the commented version and use the name of the sheet. Set xlWS = xlWB.ActiveSheet ' Set xlWS = xlWB("Sheet1") With xlWS ' You are now working with the Named file and the named worksheet ' Your Excel code begins here€¦you can even record a macro and make the process super easy!! End With ' Close and Cleanup xlWB.SaveAs xlSaveFile xlWB.Close xlapp.Quit Set xlapp = Nothing End Sub HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "sam" wrote: Can a password protected access database be connected to Excel user form? Excel user form is stored on shared drive and used by users to input data into access database. Please Help Thanks in Advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
connect excel form to access | Excel Programming | |||
connect excel form to access table | Excel Discussion (Misc queries) | |||
Cant connect to Access database from excel using vba | Excel Programming | |||
connect to access database and run a query in excel VBA | Excel Discussion (Misc queries) | |||
connect a form to excel database | Excel Discussion (Misc queries) |