ADOConnection Problem with displaying Data (1 Viewer)

silentwolf

Active member
Local time
Today, 05:39
Joined
Jun 12, 2009
Messages
545
Hi guys,

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

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

Code:
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!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:39
Joined
May 7, 2009
Messages
19,175
add Option Explicit, maybe you can find that there is an error after all.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:39
Joined
Feb 19, 2013
Messages
16,555
have you confirmed that rs is populated with records?

might be a idea also movefirst in case eof is true

Code:
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

..forever waiting... waiting for jellybean!
Local time
Today, 20:39
Joined
May 7, 2009
Messages
19,175
i don't know if you can run CopyFromRecordset on an ADODB recordset.
try using DAO.
Code:
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
 

silentwolf

Active member
Local time
Today, 05:39
Joined
Jun 12, 2009
Messages
545
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..
Code:
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

Super Moderator
Staff member
Local time
Today, 12:39
Joined
Feb 19, 2013
Messages
16,555
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

Active member
Local time
Today, 05:39
Joined
Jun 12, 2009
Messages
545
Hi again,

I have tried arnelgp version. With this modification
Code:
    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

Super Moderator
Staff member
Local time
Today, 12:39
Joined
Feb 19, 2013
Messages
16,555
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

Grover Park George
Local time
Today, 05:39
Joined
Nov 25, 2004
Messages
1,776
"...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
 

Attachments

  • 2022-03-22_16-43-21.jpg
    2022-03-22_16-43-21.jpg
    218.4 KB · Views: 225

CJ_London

Super Moderator
Staff member
Local time
Today, 12:39
Joined
Feb 19, 2013
Messages
16,555
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

..forever waiting... waiting for jellybean!
Local time
Today, 20:39
Joined
May 7, 2009
Messages
19,175
if it does not work try removing:
Code:
FMT=Delimited(;);

from sql string
 

silentwolf

Active member
Local time
Today, 05:39
Joined
Jun 12, 2009
Messages
545
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!

Code:
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

AWF VIP
Local time
Today, 04:39
Joined
Mar 9, 2014
Messages
5,425
Okay, what do you mean by 'Network Drive" - just an external shared hard drive? I just tested that and it does work for me.
 

June7

AWF VIP
Local time
Today, 04:39
Joined
Mar 9, 2014
Messages
5,425
A LAN? Unfortunately I don't have a LAN. But it should work. So, since I cannot replicate issue, sorry.
 

silentwolf

Active member
Local time
Today, 05:39
Joined
Jun 12, 2009
Messages
545
Hmm ok..

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

Thanks anyway for assitance !
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:39
Joined
Feb 19, 2013
Messages
16,555
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

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

Active member
Local time
Today, 05:39
Joined
Jun 12, 2009
Messages
545
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..
 

Users who are viewing this thread

Top Bottom