ADO Connection from Excel

  • Hi, I have the code from this link...Cool! but i need to know how to place a password line as my access database is password protected.

    Please help me out here...

    Thanks for helping in advance.


    [/FONT][FONT=Courier New]Option ExplicitSub Import_AccessData()Dim cnt As ADODB.ConnectionDim rst1 As ADODB.Recordset, rst2 As ADODB.RecordsetDim stDB As String, stSQL1 As String, stSQL2 As StringDim stConn As StringDim wbBook As WorkbookDim wsSheet1 As WorksheetDim lnField As Long, lnCount As Long[/FONT][FONT=Courier New]'Instantiate the ADO-objects.Set cnt = New ADODB.ConnectionSet rst1 = New ADODB.RecordsetSet rst2 = New ADODB.Recordset[/FONT][FONT=Courier New]Set wbBook = ThisWorkbookSet wsSheet1 = wbBook.Worksheets(1)[/FONT][FONT=Courier New]'Path to the database.stDB = "c:\db1.mdb"[/FONT][FONT=Courier New]'Create the connectionstring.stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _         & "Data Source=" & stDB & ";"         'The 1st raw SQL-statement to be executed.stSQL1 = "SELECT * FROM Production_E1"[/FONT][FONT=Courier New]'The 2nd raw SQL-statement to be executed.stSQL2 = "SELECT * FROM Production_E2"[/FONT][FONT=Courier New]'Clear the worksheet.wsSheet1.Range("A1").CurrentRegion.Clear[/FONT][FONT=Courier New]With cnt.Open (stConn) 'Open the connection..CursorLocation = adUseClient 'Necessary to disconnect the recordset.End With[/FONT][FONT=Courier New]With rst1.Open stSQL1, cnt 'Create the recordset.Set .ActiveConnection = Nothing 'Disconnect the recordset.End With[/FONT][FONT=Courier New]With rst2.Open stSQL2, cnt 'Create the recordset.Set .ActiveConnection = Nothing 'Disconnect the recordset.End With[/FONT][FONT=Courier New]With wsSheet1.Cells(2, 1).CopyFromRecordset rst1 'Copy the 1st recordset..Cells(2, 2).CopyFromRecordset rst2 'Copy the 2nd recordset.End With[/FONT][FONT=Courier New]'Release objects from the memory.rst1.CloseSet rst1 = Nothingrst2.CloseSet rst2 = Nothingcnt.CloseSet cnt = NothingEnd Sub


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!