connect excel user form to access database

[sam]

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

 

[ryguy7272]

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

 

[sam]

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]

Ok, going from Excel to Access! Check this out:
http://www.erlandsendata.no/english/index.php?d=envbadacexportado

This too:
http://www.erlandsendata.no/english/index.php?d=envbadacexportdao

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 OLEDBatabase 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---

 

[sam]

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]

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