import csv file to access table via excel

[neemi]

Hello all,

I am trying to import a cav file into an access table via an excel userform using vba and was wondering if someone can help me out with coding this please.

I have got an connection to the access table using the following code.

'// make Connection to access Database
    cs = BuildConnectionString(eDBType.Access)
    If cs <> "ERROR" Then
        cn.Open cs
    Else
        Err.Description = "Unable to Make Connection to Access 'Commitments' Database"
        Err.Number = 19990
    End If
  
   
    cn.Execute "DELETE * FROM tblStockOnHand"

   *** NEED to import csv to tblStockOnHand ***

Help appreciated...

Neemi

 

[Andrzejek]

Try something like:

cn.Execute "DELETE * FROM tblStockOnHand"

*** NEED to import csv to tblStockOnHand ***
Dim strTextLine As String
Dim strArray() As String
Dim strInsert As String

Open "C:\YourFile.csv" For Input As #1
Do While Not EOF(1)   [green]' Loop until end of file[/green]
   Line Input #1, strTextLine   [green]' Read line into variable[/green]
   strArray = Split(strTextLine, ",")[green]'Split into separate fields [/green]

   strInsert = "INSERT INTO tblStockOnHand " _
       & " Values('" & strArray(0) & "', " _
       & " " & strArray(1) & ", ...)"

   cn.Execute strInsert
Loop

Close #1

In your INSERT statement you just need to format Dates, if you have any. First value is a Character with two ' around the value, second is a Number in my example.

Have fun.

---- Andy

 

[neemi]

hI YES, YOUR EXAMPLE WORKS BUT JUST TAKES SOME TIME TO RUN AS THE CSV HAS OVER 10,000 RECORDS!

 

[neemi]

iN THE PAST i HAVE ACTIVATED ACCESS COMMANDS VIA EXCEL BUT JUST CAN'T REMEMBER HOW TO DO THIS.

FOR EXAMPLE FROM THE EXCEL FRONTED TELL ACCESS TO USE ITS DOCMD.TRANSFERTEXT COMMAND BY CODING THIS IN THE EXCEL MACRO?

AND IN A SIMLAR WAY YOU CAN USE DOCMD VIA EXCEL.

DOES ANYONE KNOW WHAT I AM TALKING ABOUT?

 

[neemi]

I THINK I REMEMBER HOW TO DO IT.... got to set the relationship to the Access object library.

will test it and get back to evry1.