Insert values stored in .csv to Access Database over network with VBScript

Question

This is kind of a follow-up post to this question.

I am trying to put data from a .csv into a .mdb (MS Access 2000).

This script works perfectly fine if the DB is stored on my hard drive, but it is on a different drive I access over a network. I have full rights there and I can insert new data sets by hand without any problems.

'There are several other Subs in this .hta-file
'these two are specified along with some other public variables
'in the beginning

Const   adOpenStatic = 3
Const   adLockOptimistic = 3

Sub CSVImport

  connStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=S:\folderpath with blanks ß and ,commas\somedatabase.mdb"

  'Define object type
  Set objConn      = CreateObject("ADODB.Connection")
  Set objRecordSet = CreateObject("ADODB.Recordset")

  'Open Connection
  objConn.open connStr

  objRecordSet.Open "SELECT * FROM SomeTable", _
    objConn, adOpenStatic, adLockOptimistic

  Set objFSO = CreateObject("Scripting.FileSystemObject")

  Set objFile = objFSO.OpenTextFile("P:\someFile.csv")

  Do Until objFile.AtEndOfStream
    strVKBL_Stamm = objFile.ReadLine
    arrVKBL_Stamm = Split(strVKBL_Stamm, ";")
    objRecordSet.AddNew

    objRecordSet("ID") = arrVKBL_Stamm(0)
    objRecordSet("LastName")  = arrVKBL_Stamm(1)
    objRecordSet("Date")      = CDate(arrVKBL_Stamm(2))
    objRecordSet("More")      = arrVKBL_Stamm(...)
    objRecordSet("andMore")      = arrVKBL_Stamm(...)

    objRecordSet.Update
  Loop

  MsgBox "All done"

  Set objRecordSet = Nothing
  Set objFSO = Nothing
  Set objFile = Nothing
  Set objConn = Nothing
End Sub

My .csv-File looks like this:

50009900;Doe;01.01.12;foo;bar

I don't get any errors, while the script is running. I checked the strings stored in arrVKBL_Stamm(...) and they all are alright. My last MsgBox pops up, the script stops, nothing happened in my DB.

Any thoughts?

That is not VB.NET code – Ňɏssa Pøngjǣrdenlarp Feb 22 '16 at 13:06 — Ňɏssa Pøngjǣrdenlarp, Feb 22 '16 at 13:06
Woops. clicked the wrong box. Thanks. – Tom K. Feb 22 '16 at 13:16 — Tom K., Feb 22 '16 at 13:16
This would be better off rewritten in VB.NET – HackSlash Oct 29 '18 at 20:58 — HackSlash, Oct 29 '18 at 20:58

score 1 · Answer 1 · answered Oct 29 '18 at 20:57

I replied on your first thread but I want to reiterate here that you are reinventing a wheel that works very well as it is. I wouldn't recommend using VBScript for what you are trying to do. You could write this same code in VBA directly inside of Access itself. You don't even need to do that because SAP uses a standard database engine in the back end. You can set up linked tables that pull directly from SAP in to Access. No export\import. It's a live table.

I hate to give you "don't do that" as an answer but that is most definitely the correct answer for you.

So use a linked table directly to SAP or move this code in to Access VBA and run it from there.

score 0 · Answer 2 · answered Oct 29 '18 at 21:27

This line jumps out at me for being a static recordset adOpenStatic as that won't allow record updates on the DB, only the client side recordset:

objRecordSet.Open "SELECT * FROM SomeTable", objConn, adOpenStatic, adLockOptimistic

A dynamic or another type of cursor is what you would need:

objRecordSet.Open "SELECT * FROM SomeTable", objConn, adOpenDynamic, adLockOptimistic

Insert values stored in .csv to Access Database over network with VBScript

2 Answers2