a_ahmed
03-22-2017, 07:32 AM
I can do a select * FROM [sheetname$] it gets the entire table
I can see the fields if I go RST.Fields(0).name
However... if I try to Select 'Field name' FROM [sheetname$] I get EXPR1000 and the column name repeated for about 6-7 thousand rows...
PersonID = "'PERSON Person ID'"
PayName = "'PERSON Full Name'"
' SQLString = "Select PersonID, PayName FROM [" & PayHis.Name & "$]"
SQLString = vbNullString
Set RST = RecordSetFromSheet(PayHis.Name, FSet, SQLString)
I use this function:
Public Function RecordSetFromSheet(SheetName As String, SettingsSheet As Worksheet, Optional ByVal SQLString As String)
'Setup Objects and Variables
Dim RST As Object, CNX As Object, CMD As Object
Set RST = CreateObject("ADODB.RecordSet")
Set CNX = CreateObject("ADODB.Connection")
Set CMD = CreateObject("ADODB.Command")
Dim FileToOpen As String: FileToOpen = ThisWorkbook.FullName
Dim FileSettings(1 To 3) As String, FileExt As String
FileExt = LCase(Right(FileToOpen, Len(FileToOpen) - InStrRev(FileToOpen, ".")))
x = Application.Match(FileExt, SettingsSheet.Range("E1:E" & SettingsSheet.Cells(Rows.Count, "E").End(xlUp).Row), 0)
If Not (IsError(x)) Then
FileSettings(1) = SettingsSheet.Cells(x, "F") 'Provider
FileSettings(2) = SettingsSheet.Cells(x, "G") 'Extended Properties 1
FileSettings(3) = SettingsSheet.Cells(x, "H") 'Extended Properties 2
End If
Set x = Nothing
With CNX
.Provider = FileSettings(1)
.ConnectionString = "Data Source=" & FileToOpen & ";" & _
"Extended Properties=" & Chr(34) & FileSettings(2) & ";" & _
FileSettings(3) & Chr(34) & ";"
.Open
End With
If SQLString = vbNullString Then SQLString = "SELECT * FROM [" & SheetName & "$]"
'Setup Command
Set CMD.ActiveConnection = CNX
CMD.CommandType = adCmdText
CMD.CommandText = SQLString '"SELECT * FROM [" & SheetName & "$]"
RST.CursorLocation = adUseClient
RST.CursorType = adOpenDynamic
RST.LockType = adLockOptimistic
'Open Connection
RST.Open CMD
'Disconnect RecordSet
Set RST.ActiveConnection = Nothing
'Cleanup
If CBool(CMD.State And adStateOpen) = True Then Set CMD = Nothing
If CBool(CNX.State And adStateOpen) = True Then CNX.Close
Set CNX = Nothing
'RETURN RecordSet object
Set RecordSetFromSheet = RST
End Function
I put this function together from some I found online. I tried changing the cursor type from dynamic to static too but no change.
Help me out :(
I can see the fields if I go RST.Fields(0).name
However... if I try to Select 'Field name' FROM [sheetname$] I get EXPR1000 and the column name repeated for about 6-7 thousand rows...
PersonID = "'PERSON Person ID'"
PayName = "'PERSON Full Name'"
' SQLString = "Select PersonID, PayName FROM [" & PayHis.Name & "$]"
SQLString = vbNullString
Set RST = RecordSetFromSheet(PayHis.Name, FSet, SQLString)
I use this function:
Public Function RecordSetFromSheet(SheetName As String, SettingsSheet As Worksheet, Optional ByVal SQLString As String)
'Setup Objects and Variables
Dim RST As Object, CNX As Object, CMD As Object
Set RST = CreateObject("ADODB.RecordSet")
Set CNX = CreateObject("ADODB.Connection")
Set CMD = CreateObject("ADODB.Command")
Dim FileToOpen As String: FileToOpen = ThisWorkbook.FullName
Dim FileSettings(1 To 3) As String, FileExt As String
FileExt = LCase(Right(FileToOpen, Len(FileToOpen) - InStrRev(FileToOpen, ".")))
x = Application.Match(FileExt, SettingsSheet.Range("E1:E" & SettingsSheet.Cells(Rows.Count, "E").End(xlUp).Row), 0)
If Not (IsError(x)) Then
FileSettings(1) = SettingsSheet.Cells(x, "F") 'Provider
FileSettings(2) = SettingsSheet.Cells(x, "G") 'Extended Properties 1
FileSettings(3) = SettingsSheet.Cells(x, "H") 'Extended Properties 2
End If
Set x = Nothing
With CNX
.Provider = FileSettings(1)
.ConnectionString = "Data Source=" & FileToOpen & ";" & _
"Extended Properties=" & Chr(34) & FileSettings(2) & ";" & _
FileSettings(3) & Chr(34) & ";"
.Open
End With
If SQLString = vbNullString Then SQLString = "SELECT * FROM [" & SheetName & "$]"
'Setup Command
Set CMD.ActiveConnection = CNX
CMD.CommandType = adCmdText
CMD.CommandText = SQLString '"SELECT * FROM [" & SheetName & "$]"
RST.CursorLocation = adUseClient
RST.CursorType = adOpenDynamic
RST.LockType = adLockOptimistic
'Open Connection
RST.Open CMD
'Disconnect RecordSet
Set RST.ActiveConnection = Nothing
'Cleanup
If CBool(CMD.State And adStateOpen) = True Then Set CMD = Nothing
If CBool(CNX.State And adStateOpen) = True Then CNX.Close
Set CNX = Nothing
'RETURN RecordSet object
Set RecordSetFromSheet = RST
End Function
I put this function together from some I found online. I tried changing the cursor type from dynamic to static too but no change.
Help me out :(