0

I'm relatively new to Microsoft Access, and just a beginner with SQL programming.

I have a table in MS Access. It is an import from a .txt file. In the first column There is a String with a Number or Code in it. This number always Comes after a string "Arbeitslpatz "(The first character starts at string Position 17) and always changes every 76 rows. (Total rows 29790). What I want to achieve is to copy this number (for example 101100) and paste it in a new column in the first 76 rows. Then comes the next number for the next 76 rows, and so on. (I later want to to a lookup function with SQL WHERE functions and return some desired values, and for that I need to have these "Arbeitsplatz numbers" in a new row. I already assigned an ID to the file, but I didn't manage to get farther. (I experimented with VBA Loops, but didn' really succeed) Either SQL or VBA Code would be appreciated. enter image description here

enter image description here

I don't know how well the screenshots can be seen &/ if I provided enough Information...

Thank you all for your help

This is an Image from the orignial text file which I linkt to the database in Access:

enter image description here

  • Process your text file, perform needed action using common VBA functions, save this file back, then import (or insert data into table line-by-line during parsing loop - I think it is easiest). – Akina Jul 17 at 8:33
  • I Need to update this file daily. And saddly my VBA Knowledge is as Basic as it can be. Could you by any Chance provide a sample Code, or Concept that would give me at least an idea or a starting Point? Thanks a lot! – Neymar0001 Jul 17 at 8:45
  • Replace your source data picture with source text file sample (decrease 76 lines to 2 or 3, give 2 such data blocks - it's enough). Additionally you may share the whole file using some online service (pastebin.com for example). – Akina Jul 17 at 8:47
  • I just added a screenshot of the text file – Neymar0001 Jul 17 at 9:21
  • If You like pictures so much, why aren't You a photographer? – Akina Jul 17 at 9:23
0

Edited OP code:

Function Macro1()
'' dim variables
Dim tmp As String, temp() As String
Dim rs As Recordset
'' open source for reading
Open "J:\80_Applications\SAPGuiScripting\01_TXT_Files\CM01_Total.csv" For Input As #1
'' attach recordset to table
Set rs = CurrentDb.OpenRecordset("CM01_Total_1") 
'' loop source - read line-by-line until its end
Do Until EOF(1)
  '' read one line
  Line Input #1, tmp
  '' look if it is a header, if yes, extract and store the number
  If Left(tmp, 12) = "Arbeitsplatz" Then num = Trim(Mid(tmp, 17,8))
  '' look if it is a data line
  If InStr(tmp, "|") > 0 And InStr(tmp, "Woche") = 0 Then
    '' divide the line to separate fields
    temp = Split(tmp, "|")
    With rs
      '' add new, empty, record
      .AddNew
      '' set values into fields
      !Arbeitsplatz = num
      !Woche = temp(1)
      !Bedarf = temp(2)
      !Angebot = temp(3)
      !Belast = temp(4)
      ![freie_Kap] = temp(5)
      !Einh = temp(6)
      '' save editions
      .Update
    End With
  End If
'' loop to reading the next line
Loop
'' close source file and recordset
Close #1
rs.Close
Set rs = Nothing
End Function

PS. Error handler must be added.

  • @Neymar0001 Comments added. – Akina Jul 18 at 8:23
0

This code runs without an error, but doesn't do anything...

Option Compare Database

Function Macro1()
Dim tmp As String, temp() As String
Dim rs As Recordset
Open "J:\80_Applications\SAPGuiScripting\01_TXT_Files\CM01_Total_1.txt" For Input As #1
Set rs = CurrentDb.OpenRecordset("CM01_Total_1")
Do Until EOF(1)
  Line Input #1, tmp
  If Left(tmp, 12) = "Arbeitsplatz" Then num = Val(Mid(tmp, 17))
  If InStr(tmp, "|") > 0 And InStr(tmp, "Woche") = 0 Then
    temp = Split(tmp, "|")
    With rs
      .AddNew
      !Arbeitsplatz = num
      !Woche = temp(1)
      !Bedarf = temp(2)
      !Angebot = temp(3)
      !Belast = temp(4)
      ![freie Kap] = temp(5)
      !Einh = temp(6)
      .Update
    End With
  End If
Loop
Close #1
rs.Close
Set rs = Nothing
End Function
  • The newest error essage is: Runtime error '424'. Object required. You must dim rs as recordset, open your destination table in it before the cycle and close after. And I'd recommend to define all variables as local for your macro. – Akina Jul 18 at 6:57
  • My answer is now altered according to your code. – Akina Jul 18 at 7:09
  • @Akina Thank you SOOOOOOO much for your help! Now the Code runs without Errors, but doesn't do any modifications in my table… What am I doing wrong? (The Problem is that I don't understand what part of the Code does what, and I'm completely lost in it...) – Neymar0001 Jul 18 at 8:12
  • but doesn't do any modifications in my table It must insert new records, not modify existing ones. I don't understand what part of the Code does what I'll comment it a little. wait... – Akina Jul 18 at 8:18
  • You are a Genious!!! I don't know how can I thank you! It worked!! Thanks a 1 000 000!! – Neymar0001 Jul 18 at 8:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.