import csv files into Access with Word macro

[muyBN]

Is there a way to import csv files into Access with a Word macro?

 

[Rob]

Create the import macro in Access. From Word create an instance of Access
like so:

Dim oAccess As Object
Set oAccess = CreateObject("Access.Application")

Open the database...

oAccess.opencurrentdatabase ("<full path to DB>")

Run the Access macro...

oAccess.DoCmd.RunMacro "<macro name>"

 

[muyBN]

Thanks, Rob. Are there any additional parameters I can add so that I don't
have to go through the wizard? For example, the name of the file won't change
from day to day and the CSV file has a header that I need to assign to a DB
field of a different name.

 

[muyBN]

Rob, this is good, but since this macro will be for my boss who's confused
enough with what I'm doing, I need to keep it simple and within one
application. After much struggle and research, I came up with a way of doing
this programmatically and seamlessly, with the following code:

strSQL = "INSERT INTO [" & strTable & "] " & _
"SELECT * " & _
"FROM [Text;HDR=YES;DATABASE=" & strPath & ";].[" & strFile & "]"

Obviously, strTable, strFile, and strPath are my variables for my table
name, file name of the CSV file, and the path or directory of the CSV file
(even though it says "database"); so the person who might want to use this
will either have put their own variables in here or hard-code the values of
their particular elements.

 

[muyBN]

Of course, after defining the SQL statement, you also open the connection and
execute it:

Set objConn = CreateObject("adodb.connection")
objConn.ConnectionString = "data source=" & strDB & ";
Provider=Microsoft.Jet.OLEDB.4.0"
objConn.Open
objConn.Execute strSQL
objConn.Close