Convert Excel 2003 VBA to Access VBA 2003

[DMS500Tech]

I give up.
I have looked and serched this web site for 3 days thru 1000's of titles and dozens of code snippets and can't find what I need.
Myself and an intern put together the following code last summer (I am not a programmer it is more like I hack code together until it works). Well I have ran into a big problem the query I am extracting the data from has grown from 50 some thousand records to over 200,000 records so I have far exceeded Excels 65536 row limit.
What I need to do is convert the following Excel VBA code so that it will work the same way in Access. I can't seem to find any examples of what it is that needs to be changed or how to change it.

-------------------------------------------------
CODE AS IT IS NOW
-------------------------------------------------

Option Explicit

Sub Extract_DN_and_LEN_from_a_text_files()
'
' populate Macro
'If there are coloumns that you dont want, comment it out in the code

Dim intOutFile As String
intOutFile = FreeFile
Dim RecordNum As Long
String
Open "C:\TEMP\CR16_0.TXT" For Input As #intOutFile ' Enter path of input file

RecordNum = 1

Dim LinesFromFile, NextLine As String

' code to check if the workbbok is open and if the contents of the workbook are protected
If ActiveWorkbook Is Nothing Then Exit Sub ' no active workbook
If ActiveWorkbook.ProtectStructure Then
MsgBox ActiveWorkbook.Name & " is protected.", vbCritical, "cannot overwrite."
Close intOutFile
Exit Sub
End If

Workbooks("RESULTS.xls").Worksheets("Sheet1").Activate

With ThisWorkbook.Worksheets("sheet1")

Do Until EOF(intOutFile)
Line Input #intOutFile, NextLine

If Mid(NextLine, 1, 3) = "LEN" Then
LinesFromFile = Mid(NextLine, 10, 16)
RecordNum = RecordNum + 1
.Cells(RecordNum, 1) = LinesFromFile

ElseIf Mid(NextLine, 1, 3) = "DN " Then
LinesFromFile = Mid(NextLine, 4, 10)
.Cells(RecordNum, 2) = LinesFromFile
End If

Loop

End With
Close intOutFile
End Sub

---------------------------------------------------
HERE IS A SAMPLE OF THE QUERY
---------------------------------------------------

LEN: ABCD 01 0 11 46
TYPE: SINGLE PARTY LINE
DN 4055551234 LCC 1FR SIG DT LNATTIDX 0
XLAPLAN KEY : 405_POTS_0 RATEAREA KEY : OKCY_L536_0
IBN TYPE: STATION
CUSTGRP: RESGRP SUBGRP: 0 NCOS: 0
CARDCODE: RDTLSG GND: N PADGRP: STDLN BNV: NL MNO: N
PM NODE NUMBER : 115
PM TERMINAL NUMBER : 1147
OPTIONS:
LCDR COD DGT PIC 6269 Y LPIC 6269 Y
RES OPTIONS:
CNDB NOAMA CND NOAMA CNAMD NOAMA ACRJ INACT
OFFICE OPTIONS:
U3WC AIN LNPTRIG
---------------------------------------------------

If anyone can help with this I would be very gratefull

 

[Darrylles]

Hi DMS,

Good to hear that you use the search capabilities - rather than simply ask a question that MAY have been answered 10 times already.

Sorry, but there just aren't any 'magic' wizards to do this. Usually, part of a 'programmers' job is to estimate and foresee data-growth. That's why their skills are required, and, in this case - database use would have been envisaged.

Firstly - you must define the tables, then come back with questions, or, come back with questions about defining the tables themselves.

ATB

Darrylle




Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[fneily]

Work the same way in Access? Are you planning to just copy the excel spreadsheet data into an Access table and not normalize? WRONG!! Access tables ARE NOT like excel spreadsheets. Access is a relational database management system and the tables must be built along certain rules (normalization). I'm sure someone will post they copy excel spreadsheets to access all the time and have no troubles. That's because they don't do any advanced analysis on the data.
My suggestion: Copy and paste the excel spreadsheets data into an access table and then run the Table Analyzer wizard. Tools - Analyze - Table. Click Next. Click Next. Select the table and click Next. Choose Yes. Let the Wizard Decide option and click Next. Rename the tables and click next. Confirm the primary keys and click next. Choose No, Don't Create The Query option and click Finish.
Now you can write some Access queries to get any info you want.
And as the above post suggested, PRE-PLAN.

 

[DMS500Tech]

My bad
I forgot to mention a few critical items.

1st the file that the Excel spread sheet gets its data from is a 197Mb text file generated from a Procomms Aspect script that runs every morning (which use to be a lot smaller, by about 80%). The blank sheet is opened, then the VBA Macro is ran and it datafills the cells with the LEN and DN. Now that the file is bigger the script errors out when it reaches 65,536 lines.

2nd I do have an Access database created with a defined table and columns

3rd I think I figured out how to open an external text file and assign the first line to a variable

What I am really having a hard time with is how to translate

RecordNum = RecordNum + 1
.Cells(RecordNum, 1)= LinesFromFile

to an Access Table\column\row\.

In other words what is the "Put this data here" command that is used in Access VBA?
How do I take the contents of a variable and insert it into a table in a specific place

I hope this is clear now. It is hard to get a good responce when you take it for granted everyone else should know things not mentioned, sorry.

 

[Darrylles]

Hi DM,

OK that's different.

Are you aware that you can produce a link from Access to an external file such as Excel or a text file (as long as the structure is table-like and consistent)?

This 'link' can then be treated as if it is an Access table.
Thus, you can access the file just like an Access table, and copy data to internal normalized tables.

Also, as the external application updates the external file - Access sees those changes.

See MS Access -> File -> Get External Data -> Import to import the file into a 'ready-made' table - this is a manual process, or MS Access -> Objects -> Tables -> New -> Link table to produce a linked table from an external file.

Regards,

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.