ADODB Connection - Header text not extracted & read-only issue

Question

I am delving into the world of VBA data connections, and would appreciate some assistance. The code below is what I have so far, but there are a couple of oddities I can't figure out.

Sub sbADO()
    Dim sSQLQry As String
    Dim ReturnArray
    Dim Conn As New ADODB.Connection
    Dim mrs As New ADODB.Recordset
    Dim DBPath As String, sconnect As String

    DBPath = "C:\USERS\NAME\DOCUMENTS\VBA Work\Data Source.xlsx"
    sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"
    Conn.Open sconnect

    sSQLQry = "SELECT * From [Sheet1$]"
    mrs.Open sSQLQry, Conn
    Sheet3.Range("A1").CopyFromRecordset mrs
    mrs.Close
    Conn.Close
End Sub

This code works, however:

The data pulled in doesn't include Row1 of the dataset (so the headers aren't pulled in)
If the source workbook 'Data Source.xlsx' is open. The code will cause the workbook to open again but in read-only mode. Can this be avoided?
Can the connection string be edited so that the source file is never locked out? ie. queried in Read-Only mode other users can open it whilst the query is being completed?

Any help is appreciated Thanks Caleeco

The schema metadata is never part of the result set. Iterate the Fields collection. — Mathieu Guindon
– Mathieu Guindon, Commented Jan 31, 2018 at 22:02
What's the problem with opening the workbook in read-only mode? The file is locked for editing by another process/user - you can't acquire a write lock on it. Aren't you reading from it anyway? Does it still pop a message if you specify Mode=read; in the connection string? — Mathieu Guindon
– Mathieu Guindon, Commented Jan 31, 2018 at 22:04
@Mat'sMug thanks for the replies. Ah ok, i'm very new to dealing with recordsets & connection strings. Two problems here. If I pause the code after executing this line Conn.Open sconnect, then try to open the source file Data Source.xlsx i get the File is Locked by Another User warning message. This is undesirable as I have many people on the network that need Write access to that source file. Not too concerned about a second instance being opened as Read-only if someone ALREADY has the file locked out as I can close it with VBA after. — Calico
– Calico, Commented Jan 31, 2018 at 22:13
In response to your edit. I tried changing the conn string to sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';Mode=Read;ReadOnly=True;" but still get the same problem of locking out this file while the query is executed. — Calico
– Calico, Commented Jan 31, 2018 at 22:14
Set mrs = Nothing and then Set Conn = Nothing after you close both, that should put the final nail in these objects' coffins. TBH the best solution is to put that data where it belongs: in a database. Even MS-Access would do. — Mathieu Guindon
– Mathieu Guindon, Commented Jan 31, 2018 at 22:36

score 1 · Accepted Answer · 2018-02-01 01:09:38Z

1

Try this:

Sub sbADO()
  Dim sSQLQry As String
  Dim ReturnArray
  Dim Conn As New ADODB.Connection
  Dim mrs As New ADODB.Recordset
  Dim DBPath As String, sconnect As String,i as integer

  'DBPath = ThisWorkbook.FullName
  DBPath = "C:\USERS\NAME\DOCUMENTS\VBA Work\Data Source.xlsx"
  sconnect= "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""" & _
     DBPath & """;Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX = 1"""

  Conn.Open sconnect

  sSQLQry = "SELECT * From [Sheet1$]"
  mrs.Open sSQLQry, Conn
  if rs.recordcount>0 then 
     rs.movefirst
     for i=0 to rs.fields.count-1
       'read here the headers and add them to your sheet in row 1
       Sheet3.Cells(1, i + 1) =rs.Fields(i).Name
     next
  end if
  Sheet3.Range("A2").CopyFromRecordset mrs
  mrs.Close
  Conn.Close
End Sub

Collectives™ on Stack Overflow

ADODB Connection - Header text not extracted & read-only issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related