VBA function declaration to create a query that links to a CSV file Ms Access Gurus

Help support this site, thank you.

Home Articles Code Tools

VBA to create SQL statement that links to a CSV file

I like to link to CSV files using a query instead of a linked table because it's a lot easier to set up programmatically.

Here is VBA code you can use in your projects. After constructing the SQL, it calls a procedure to make a query, which is fine in Access. However, if you're in Excel, instead of making a query with the SQL, use the SQL to open a recordset.

Use VBA to create SQL that links to a CSV file

Quick Jump

Download VBA Reference Backstory Share Donate

Goto Top  

Download

Download zipped BAS file you can import into your VBA projects for Access. Modify for Excel and other apps to open a recordset instead of making a saved query. mod_GetSQL_LinkCsv_s4p.zip

If you have trouble with a download, you may need to unblock the ZIP file, aka remove Mark of the Web, before extracting the file. Here are steps to do that: https://msaccessgurus.com/MOTW_Unblock.htm

Goto Top  

VBA

Standard module 

' module name: mod_GetSQL_LinkCsv_s4p
'*************** Code Start ***************************************************
' Purpose  : Function to create and return SQL to link to a CSV file
' Author   : crystal (strive4peace)
' Code List: www.msaccessgurus.com/code.htm
' This code: https://msaccessgurus.com/VBA/SQL_LinkCsv.htm
' LICENSE  :
'   You may freely use and share this code, but not sell it.
'   Keep attribution. Mark your changes. Use at your own risk.
' Then use the SQL to create a saved query or to open a recordset
'--------------------------------------------------------------------------------
'                              GetSQL_LinkCsv_s4p
'--------------------------------------------------------------------------------'
Public Function GetSQL_LinkCsv_s4p( _ 
   psPath As String _ 
   ,psFilename As String _ 
   ) As String 
'230131 strive4peace
   Dim sConnect As String 
   
   sConnect =  "[Text;DATABASE=" _ 
      & psPath _ 
      &  "].[" & psFilename _ 
      &  "]"
   
   GetSQL_LinkCsv_s4p =  "SELECT Q.* FROM " & sConnect &  " as Q;"
   
End Function 
'--------------------------------------------------------------------------------
'                             testSQL_LinkCsv_s4p
'--------------------------------------------------------------------------------'
Sub testGetSQL_LinkCsv_s4p() 
   Dim sPath As String _ 
      ,sFile As String _ 
      ,sSQL As String 
   
   sPath =  "C:\MyPath"        '------------ customize
   sFile =  "MyFilename.csv"   '------------ customize
   
   'Call GetSQL_LinkCsv_s4p
   sSQL = GetSQL_LinkCsv_s4p(sPath,sFile) 
   
   MsgBox sSQL,, "done"
End Sub 

'*************** Code End *****************************************************
' Code was generated with colors using the free Color Code add-in for Access.

Goto Top  

Reference

Microsoft

QueryDef.Connect property (DAO)

Wikipedia

Comma-separated values

Goto Top  

Backstory

I'm writing a program to loop through a folder and get data from CSV files. Saved queries were easier to create with VBA than linking to tables And I didn't want to import all that either since it was going to be shuffled around.

The next step is my process is looping through each file of a folder and making queries for all the CSV files. Then the data can be analyzed and moved to other places.

VBA code to make a query is posted here: https://msaccessgurus.com/VBA/Query_Make.htm

If you like this page, please let me know. Donations, big and small, are much appreciated! They all say Thank you.

Share with others

Here's the link for this page in case you want to copy it and share it with someone:

https://msaccessgurus.com/VBA/SQL_LinkCsv.htm

or in old browsers:
http://www.msaccessgurus.com/VBA/SQL_LinkCsv.htm

Get Help with Access and maximising the power of queries

Let's connect and team-develop your application together. I teach you how to do it yourself. My goal is to empower you.

While we build something great together, I'll pull in code and features from my vast libraries as needed, cutting out lots of development time. I'll give you lots of links to good resources.

Access is great at many things and can link to data in all kinds of different places. Access is access! I'd love to help you. Email me at training@msAccessGurus.com

~ crystal

the simplest way is best, but usually the hardest to see

Goto Top