How to prevent Access change string to date (1 Viewer)

dhlao

Registered User.
Local time
Yesterday, 18:14
Joined
Dec 23, 2014
Messages
37
I wrote a VBA code to get the field value of a csv file, then use it to update the database table. I paste some of my code below
Code:
Dim objConn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Dim objUpdate As ADODB.Connection

objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & theFolderName & ";Extended Properties='text;HDR=Yes;FMT=Delimited'"
Set objRs = objConn.Execute("SELECT * FROM " & theFileName)
Set objUpdate = CurrentProject.Connection

Do Until objRs.EOF
        strSql = "UPDATE [26e)InstSourceComponentRequests] SET [Section] = '" & objRs(2) & "' WHERE [Program] = '" & objRs(0) & "' AND [Course] = '" & objRs(1) & "';"
        objUpdate.Execute strSql
        objRs.MoveNext
Loop
1. The target of this code is to set the [Section] value in the table
2. The [Section] value is at the 3rd column inside the csv file
3. By using the UPDATE query, it map the [Section] value to the corresponding [Program] and [Course] in the table

This code did the job. But the problem is the string of the [Section] value is like "2-22-01". But after update to the table, the value become "2/22/2001".

The value is a string text, not date. How can I prevent this happen ?:banghead:

I just attached 2 files, 1 mdb and 1 txt (change the extension to csv before test).
 

Attachments

  • demo.mdb
    1.5 MB · Views: 223
  • testCsv.txt
    2.8 KB · Views: 233
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Today, 12:14
Joined
Jan 20, 2009
Messages
12,849
Import the csv as text using an import specification or schema.ini file do define the data type.
 

dhlao

Registered User.
Local time
Yesterday, 18:14
Joined
Dec 23, 2014
Messages
37
Thanks Galaxiom.
I use the method of "schema.ini" (This is the 1st time I use this stuff)
One more question. About the HDR settings. Should it be "Yes" or "No" ?
Since I still want to keep the header row in my csv file
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 12:14
Joined
Jan 20, 2009
Messages
12,849
HDR is Header.

"Yes" means the csv file has a row at the top with column names.
 

Users who are viewing this thread

Top Bottom