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?
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With