ADOConnection Problem with displaying Data

[silentwolf]

Hi guys,

not sure why this code is not displaying data from CopyFromRecordset.

Private m_appExcel As Excel.Application
Private m_wkbExcel As Excel.Workbook
Private m_wksExcel As Excel.Worksheet
Private m_wksRange As Excel.Range
'

Sub GetDataFromCSV()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    Set cn = New ADODB.Connection
    
    cn.ConnectionString = _
        "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
        "Dbq=Y:\BUSINESS\Data\2021\;" & _
        "Extensions=asc,csv,tab,txt;"

    cn.Open
    
    Set rs = New ADODB.Recordset
    
    rs.ActiveConnection = cn
    rs.Source = "SELECT * FROM [OktbisDez2021.csv]"
    rs.Open
    
    Set m_appExcel = HoleAnwendung("Excel.Application")
    Set m_wkbExcel = m_appExcel.Workbooks.Add
    Set m_wksExcel = m_wkbExcel.Worksheets(1)
    
    Set m_wksRange = m_wksExcel.Range("A2")
    
'    m_wksRange.Value = "Test"
    
    m_wksRange.CopyFromRecordset rs
    m_appExcel.Visible = True
    rs.Close
    cn.Close
    
End Sub

If I add the Value "Test" it workes but it does not display the data from the Recordset?
Just not sure why it does that?

Does anyone can spot the Problem?

No Error Message it runs through not a problem but just don't display the data from the given File.

Thanks for assistance!

 

[June7]

Is HoleAnwendung() a custom function or is this maybe German for CreateObject? When I use CreateObject, code works for me. Except the first row of data is not copied. Seems CopyFromRecordset assumes the first row in file is column headers. Review https://docs.microsoft.com/en-us/office/vba/api/Excel.Range.CopyFromRecordset

But why use CopyFromRecordset? A csv file will open directly into Excel.

 

[arnelgp]

add Option Explicit, maybe you can find that there is an error after all.

 

[CJ_London]

have you confirmed that rs is populated with records?

might be a idea also movefirst in case eof is true

rs.ActiveConnection = cn
rs.Source = "SELECT * FROM [OktbisDez2021.csv]"
rs.Open
if rs.recordcount>0 then
   .movefirst

   Set m_appExcel = HoleAnwendung("Excel.Application")
   ...
   ...
else
   msgbox "no records found"
end if

 

[arnelgp]

i don't know if you can run CopyFromRecordset on an ADODB recordset.
try using DAO.

Sub GetDataFromCSV()
    
    
    'Dim cn As ADODB.Connection
    'Dim rs As ADODB.recordSet
    '
    'Set cn = New ADODB.Connection
    '
    'cn.ConnectionString = _
    '    "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
    '    "Dbq=Y:\BUSINESS\Data\2021\;" & _
    '    "Extensions=asc,csv,tab,txt;"
    '
    'cn.Open
    '
    'Set rs = New ADODB.recordSet
    '
    'rs.ActiveConnection = cn
    'rs.Source = "SELECT * FROM [OktbisDez2021.csv]"
    'rs.Open
    
    'arnelgp
    Dim sql As String
    Dim db As DAO.Database
    Dim rs As DAO.recordSet
    
    sql = "select * " & _
    "from [Text;FMT=Delimited(,);HDR=Yes;IMEX=2;ACCDB=YES;DATABASE=Y:\BUSINESS\Data\2021\].[OktbisDez2021#csv]"
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(sql)
    
    Set m_appExcel = HoleAnwendung("Excel.Application")
    Set m_wkbExcel = m_appExcel.Workbooks.Add
    Set m_wksExcel = m_wkbExcel.Worksheets(1)
    
    Set m_wksRange = m_wksExcel.Range("A2")
    
'    m_wksRange.Value = "Test"
    
    m_wksRange.CopyFromRecordset rs
    m_appExcel.Visible = True
    rs.Close
    'cn.Close
    Set db = Nothing
End Sub

 

[CJ_London]

i don't know if you can run CopyFromRecordset on an ADODB recordset.

yes, you can

Range.CopyFromRecordset method (Excel)

Office VBA reference topic

docs.microsoft.com

 

[silentwolf]

Hi guys,

wow so many replies.

Well I am just learing it and was working with a "tutorial".
As the tutorial is out from Excel and I am trying to use it in Access I modified the code to "my Needs" .. It opens the Excel Application all fine
and insert the "Test" as mentioned but for some reason I am not getting the values of the File.


@June7

Yes it is a custom function to createObject. And it works fine..

Function HoleAnwendung(strName As String) As Object
    On Error Resume Next
    Set HoleAnwendung = GetObject(, strName)
    If HoleAnwendung Is Nothing Then
        Set HoleAnwendung = CreateObject(strName)
    End If
End Function

@arnelgp
Thanks for pointing out the same in ADO I will try that as well.

@CJ_London

have you confirmed that rs is populated with records?

You mean debug and Local Window?

 

[CJ_London]

You mean debug and Local Window?

that's one way to do it - should be in your normal toolbox of things to try when developing

or you can put a breakpoint in and step though the code, checking values on each line as you step through

or just modify your code to include those checks as per the code I provided

 

[silentwolf]

Hi again,

I have tried arnelgp version. With this modification

    sql = "select * " & _

   "from [Text;FMT=Delimited([B];)[/B];HDR=Yes;IMEX=2;ACCDB=YES;DATABASE=Y:\BUSINESS\Data\2021\].[OktbisDez2021#csv]"
[/QUOTE]

Changed to a semicolon

as it is a german version..

[B]Also I am not sure why it is a "#"csv, rather then a dot(.)csv[/B]
 
It does copy the data but when it opens it shows all in one column,

Does somewhere here need to be Local:= True as well?

pretty new to connection strings.

And what are the benefits of doing it like that?

With my code it did manage to insert one line of the file in "Range("A2")" but with not even all the data.

Need to look more into it..

But thanks for pointing things out for me.,..

and CJ' Iam also trying your version but still not quite there yet.

 

[CJ_London]

I have a similar version to Arnel's but simpler

SELECT * FROM [TEXT;DATABASE=Y:\BUSINESS\Data\2021\;HDR=Yes].OktbisDez2021.csv

I always alias it as sometimes you can get issues

SELECT *
FROM (SELECT * FROM [TEXT;DATABASE=Y:\BUSINESS\Data\2021\;HDR=Yes].OktbisDez2021.csv) AS txt;

IMEX relates to excel files so not sure why it has been included

 

[GPGeorge]

"...IMEX relates to excel files so not sure why it has been included"

I'm not sure that's quite true. Linked SharePoint lists also have the IMEX argument. It does NOT appear to impact updatability, though. I have tried with all three settings-- IMEX=0, IMEX=1, and IMEX=2

 

[CJ_London]

I've not used sharepoint so wasn't aware it is used there. With excel, setting IMEX=0 enables access to update excel directly using sql or by manually editing the query (at least as a dao recordset, not tried it with ado) but for the purposes of importing data, doesn't really matter which one you use although setting IMEX=1 will set the datatype to text for a column which has a single text value in the first 8 rows rather than majority which applies for IMEX=2. Interested to know if the same applies with sharepoint.

 

[arnelgp]

if it does not work try removing:

FMT=Delimited(;);

from sql string

 

[silentwolf]

Hi again,

I have been trying and still not really any luck with getting the data from ADO Connection String.

For testing purpose I did try in Excel instead of Access with all the same as it has been done on the tutorial Video.

As "WiseOwl" Andrew has done it in his Video. Great tutorials by the way I find!

Sub GetDataFromCSV()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cn = New ADODB.Connection

    Debug.Print ThisWorkbook.Path

    cn.ConnectionString = _
        "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
        "Dbq=" & ThisWorkbook.Path & "\My Files\;" & _
        "Extensions=asc,csv,tab,txt;"
        
    cn.Open
    
    Set rs = New ADODB.Recordset
    
    rs.ActiveConnection = cn
    rs.Source = "SELECT * FROM [OktbisDez2021.csv]"
    rs.Open
    
    Tabelle1.Range("A2").CopyFromRecordset rs
    
    rs.Close
    cn.Close
End Sub

So what I did was fallowing.
Created a Folder on my Network Drive Called "Y:\Business\Programming\CSV_Daten"
In that Folder the "CSV_ADO.xlsm" is located.

In that folder I created another Folder "My Files" "Y:\Business\Programming\CSV_Daten\My Files"
And in My Files is "OktbisDez.2021.csv" File saved. "Y:\Business\Programming\CSV_Daten\My Files\OktbisDez.csv"

If I step through the code there are no errors and I do have always "Option Explicit" on.

So what I am not sure of how do you test it and what does the Local window can tell me about the recordset or what issue there is?

I just like to understand it and hopefully understand how I am able to fix that problem for this and future issues.

Is it possible that it does not work because it is on a Network Drive?

Currently I am gettin one Record in "A2" after many minutes of calculating... but it is only one record and also all in one cell rather then split into
all columns.
Cheers

 

[June7]

Okay, what do you mean by 'Network Drive" - just an external shared hard drive? I just tested that and it does work for me.

 

[silentwolf]

No not external a Network Drive "Server"

 

[June7]

A LAN? Unfortunately I don't have a LAN. But it should work. So, since I cannot replicate issue, sorry.

 

[silentwolf]

Hmm ok..

Not sure where the issue is.. but I will keep trying..

Thanks anyway for assitance !

 

[CJ_London]

If I step through the code there are no errors and I do have always "Option Explicit" on.

So what I am not sure of how do you test it and what does the Local window can tell me about the recordset or what issue there is?

I'm not going to say this again - you check whether the recordset has any rows (recordcount), you check those rows have data
?rs.recordcount
?rs!nameoffield1
?rs!nameoffield2

if no rows then nothing to copy, if the data is blank, you have no data. if the first field contains the values for the entire row then it is not a .csv file (comma separated) etc. Perhaps you need a schema.ini file to specify the columns if the separator is not a comma or it's tab delimited. If you have not used a schema.ini file before, see this link

Schema.ini File (Text File Driver) - Open Database Connectivity (ODBC)

Schema.ini File (Text File Driver)

docs.microsoft.com

tho' I don't know if this works with ADO, but don't see any reason for it not to, I always use DAO to open text files.

If you have opened your .csv file with excel and then saved, even if you have not made any changes, it can modify the file so the TEXT driver will no longer work as expected.

 

[silentwolf]

Thanks for the link I will look into it!

I'm not going to say this again - you check whether the recordset has any rows (recordcount), you check those rows have data
?rs.recordcount
?rs!nameoffield1
?rs!nameoffield2

I will try

Cheers..