ADO cannot connect to Database

[Mike2502]

Hi

I'm trying to connect to an external file called 'Employee List' and the worksheet is called 'All Staff Members' but it doesn't seem to connect?

Option Explicit

'Add reference for Microsoft Activex Data Objects Library

Sub sbADO()
Dim sSQLQry As String
Dim sSQLSting As String
Dim ReturnArray

Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset

Dim DBPath As String, sconnect As String

DBPath = ThisWorkbook.FullName

'You can provide the full path of your external file as shown below
'DBPath = "C:\Users\me\DataNow\Home\Desktop\documents\projects\database\copy"

sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"

Conn.Open sconnect
 sSQLSting = "Select [ID], [Leave Reason], [Ops Director], [Forename], [Surname], [Date of birth], [Start Date],[Job Title] from [All Staff Members$] WHERE [ID] in ('" & _
Join(Application.Transpose(Range("A2:A50").Value), "','") & "')"
 
    mrs.Open sSQLSting, Conn
        '=>Load the Data into an array
        'ReturnArray = mrs.GetRows
                ''OR''
        '=>Paste the data into a sheet
        ActiveSheet.Range("A2").CopyFromRecordset mrs
    'Close Recordset
    mrs.Close

'Close Connection
Conn.Close

 

[Mike2502]

I think you may need some single quotes around the reference to the external workbook and worksheet like below

Range("I2").Formula = "=IF(VLOOKUP($A2,'[Copy.xlsx]Staff'!$1:$1048576,24,FALSE)="""","""",VLOOKUP($A2,DataSheet!A$1:Y$700,24,FALSE))"

Hi Juda,

Just played around with the coding you gave above, when I run the macro it brings 3 rows of random data when there isn't nothing in the A column... I've tried Mrs.Close and Conn.Close instead of setting it to Nothing to see if it would make a difference but no, could you see what might be causing this?

Cheers

Option Explicit

Sub sbADO()

Worksheets("ADO").Unprotect Password:="lol"
  Dim Conn As Object, Comm As Object, mrs As Object

  ' Removes the need to reference external library
  Set Conn = CreateObject("ADODB.Connection")
  Set Comm = CreateObject("ADODB.Command")

  Dim DBPath As String
  DBPath = "C:\Users\me\DataNow\Home\Desktop\documents\projects\References\HR Staff Employee List.xlsx"

  With Conn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Properties("Extended Properties") = "Excel 12.0 Xml; HDR=Yes"
    .Properties("Data Source") = DBPath
    .Open
  End With

  With Comm
    .ActiveConnection = Conn
    .CommandText = "SELECT [National Insurance], [Leave Reason], [Ops Director], [Forename], [Surname], [Date of birth], [Start Date],[Job Title] FROM [HR Staff Employee List$] WHERE [National Insurance] IN ('" & _
                   Join(Application.Transpose(Range("A2:A50").Value), "','") & "')"
    Set mrs = .Execute
  End With

  If Not (mrs.BOF And mrs.EOF) Then
    ActiveSheet.Range("A2").CopyFromRecordset mrs
    Close
  End If

mrs.Close
Conn.Close

  'Set mrs = Nothing
  'Set Conn = Nothing

End sub

 

[juddaaaa]

Without seeing your data, it's difficult to say.

Could you post a small sample of the Worksheet you're trying to connect to (excluding any sensitive data)?