AccessDiscussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
I am charged with the responsibility of creating a database that imports data from text files. One of the files is longer than 65,536 rows but if I open it in notepad, I can see all the rows.
Is it possible to write a vba procedure to automatically open the text file in notepad, and then populate an access table with this data?
I am very grateful for any help with this project.
Thanks again. However, I am confused by the suggestion.
I cannot see how the text file can be saved as an OLE object. I've tried using "Save As" but it doesn't give that option. Even if I could, I don't want to edit the document on Notepad, as it appears more-or-less fine (there is another problem at that point but I need to take one-step-at-a-time, as I am new to Access).
Essentially, I seek to have a button on a form that, when clicked, automatically launches Notepad, opens the large file (85,000 rows) and then imports it into Access.
The entire project captures 13 daily files describing "Corporate Actions". The files are produced from an out-of-state mainframe and sent to us in Excel (they seem to be text-type, although the "file properties" calls them the current date!).
The actual data consists mostly of words and some numbers. Some of the fields are long (100 charcaters), others are very short (1 character). The vast majority of the rows consist of 4 columns. The others range from 20 columns to 82 columns.
Management wants to capture this daily info in an Access database.
If I attempt to import the data of the largest file (85000 rows) into its particular Access table, only the first 65,536 rows are opened. If I open the file with Notepad, the entire 85,000 rows are opened. It seems that I need to open the file in Notepad (from Access) and then import the file to its particular Access table.
This is the process that I want to automate. I do not want to "save" the rows "line-by-line" into Notepad: I just want to open it with notepad and then save it to Access.
Here's how you do it. This is the On_Click for your form's button.
Dim strquote As String
strquote = """"
Open "C:\TESTFILE.txt" For Input As #1 ' Open file.
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, TextLine ' Read line into variable.
SQL = "INSERT INTO [Text Table] ( [text line] ) " _
& "SELECT " & strquote & TextLine & strquote & " AS Expr1;"
Close #1 ' Close file.
C:\Testfile is the name of your file.
Since you have 13 of these you can replicate this. It doesn't use notepad, it opens the file directly.
Try one and see if it works.
any trouble, email me directly @ kevinpreilly at aol