Importing data.....in brief

[Richio]

Before I get into my next project I was wondering if anyone could give me a few pointers. I don't need full details at this point, just to know if it can be done and a guide as to what functions to use.

Here goes....

I have a "purchase order" prepared by another department. Typical set up is... qty, product code, total price for about 30/40 items for each order.

Its prepared in notepad (it just is....don't ask) but I can convert it into excel into columns OK (if this makes life easier)

If I set up a table in Access, can I easliy import each new order and add it to the table.

So I end up with a unique order No for each order with all the related records.

thanks

 

[The_Doc_Man]

If you can get it into Excel, it is fairly easy because each cell is randomly (i.e. directly) addressable.

You create an Excel application object.

You then have to open a workbook and activate a worksheet.

Once you have a valid, active sheet, the contents of the cells are either


ActiveSheet.Rows(rowindex).Cells(columnindex)

or

ActiveSheet.Columns(columnindex).Cells(rowindex).

So you can do a For loop to scan by row or by column to find the data you want.

Just remember to close files when done with them. Otherwise you eventually run out of system resources and get nasty error messages.

 

[BLeslie88]

Wait up.....

First off do not import into excel... There is no reason you should not be able to create specifications to import it into Access.... Ensure you tie each detail record with the purchase order number...
Unique values...:
A) Suggestion create a form and use VBA and design you own, such as...
Dim db as Database
Dim rsAddnew As DAO.Recordset
Dim rsTblImp As DAO.Recordset
Dim Last As Long
Set db = CurrentDb()
Set rsAddnew = db.OpenRecordset(tblPO, dbOpenDynaset)
Set rsTblImp = db.OpenRecordset(tblImpPO, dbOpenDynaset)

that should get you started...

B) Easy but may break I worn you is the autonumber ID... It has broken on me on 2 occasions..... GGGRRRRRR! Dang Microsoft... lmao

PS. I just read Doc's reply, and yes if you can mannage in excel go ahead.... I do most of my work in access..
Regards,

 

[The_Doc_Man]

BLeslie, normally I would do as you suggested. But Richio's data is already in NOTEPAD. I don't know that NOTEPAD follows the Component Object Model so direct import that way wouldn't work. Richio suggested that Excel works and that would be a perfectly acceptable solution.

I would do it a different way but only because I already have a toolkit module full of text parsing tools. I would write a VBA module, open the NOTEPAD file in text mode with Open for Input as File 1 or something similar. (After all, it IS supposed to be just text.)

Then I would use VBA to read each record, then parse it out into its parts myself. But Richio doesn't have my toolkit and I am not allowed to send code like that through E-Mail. (I work at a US Government site. They get kind of hinky when code starts leaving the place.)

 

[Richio]

thanks for your comments, it can be done.....fantastic. I am not sure when I will get to start working on this ...so I may need a bit more help at some point.

PS - Is there anything that can't be done in Access?

 

[BLeslie88]

Well....

I would say no, within reason..... I mean obviously there are other DB's such as Oracle/AS400's/SQL Server ect... that are better for huge volume data structures...

Access is user friendly, easy to use. VB like coding and works quite well considering microsoft takes care of it... lol

Later.....