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
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).
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
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
Last edited: