need learned opinion on importing in Access

[ndendrinos]

Hello to all
I have put together thanks to many kind contributions from this forum an ordering/billing system.
I am very happy with its functionality but cannot help worrying on this point.

I use Excel to process the orders and then I transfer the records of the transactions inside
An Access database by means of linking the two.

I would prefer the import method instead of the linking one for the reason that if something happens to my excel file all will be lost in the Access database as well.

Tried the import way but I cannot just add the data as I go along , rather I would have to import the whole thing each time creating a new table and deleting the old one.

That seems wrong and I need your expert opinion on that point or any suggestions you may want to throw my way.
Thank you

 

[SydneyGeek]

Hello to all
1. I use Excel to process the orders and then I transfer the records of the transactions inside
An Access database by means of linking the two.

2. I would prefer the import method instead of the linking one for the reason that if something happens to my excel file all will be lost in the Access database as well.

3. Tried the import way but I cannot just add the data as I go along , rather I would have to import the whole thing each time creating a new table and deleting the old one.

Some comments:
Point 2 -- Importing definitely is better, in terms of speed and usability. As for losing the data, that's what backups are for :wink:

Point 3 -- There's a couple of ways for you to go on this one. Both involve marking records in another column -- 1 for sent, 0 or blank for new.
A: If you want to run it from Access, you can set up an Append query that grabs only unmarked or 0 values in the flag field, then adds them to the existing table.
B: If you want to do the update from Excel instead, consider using ADO to push the unflagged records to the Access database. It's quick (I get a about 200 records /sec transferred to a network drive, using P4 hardware) and can be push-button automated.
Regardless of whether you use A or B, flag the imported records so they don't get inserted twice.

Post back if you need more details.
Denis

 

[ndendrinos]

Hello Denis,
Thank you for your reply.
While I can manage at the excel level somewhat,when it comes to Access I'm in the dark.
There is a solution then and that is to export from excel to access.
I really do not follow you at all so maybe if you have the time you might want to explained in great details how to go about it.
Right now there are about 30 macros in the excel file doing just about anything under the sun.
In book "ND" Sheet 1 is where I type the invoice.
One of the macros inserts in book "Total Sales"on Sheet "All Records" the details of each invoice (Range = A:N)
This is the sheet that is linked to the access database table titled :you guessed it.."All Records"

If you could explain how to achieve the equivalent of copying each new row from excel and pasting it in the access table by means of a macro I guess ? that would be great.
Best regards,
Nick (ex ANZ bank employee 1966)

 

[SydneyGeek]

Hi Nick, here's an example of what you can do.

EDIT: You need to add a reference to Microsoft ActiveX Data Objects 2.1 Library (or higher) or else you'll get nowhere ...

Sub AddRecords()
  Dim cnn As ADODB.Connection
  Dim rst As ADODB.Recordset
  Dim WSOrig As Worksheet
  Dim WSTemp As Worksheet
  Dim sSQL As String
  Dim FinalRow As Long
  Dim MyConn
  Dim i As Long
  
  Set WSOrig = ActiveSheet
  FinalRow = Range("A65536").End(xlUp).Row
  
  'define the path to the database and open a connection to the database
  MyConn = "H:\my database.mdb" 'change to suit your setup
  
  Set cnn = New ADODB.Connection
  With cnn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Open MyConn
  End With
  
  'define the recordset
  Set rst = New ADODB.Recordset
  rst.CursorLocation = adUseServer
  
  'open the table
  rst.Open Source:="All Records", _
    ActiveConnection:=cnn, _
    CursorType:=adOpenDynamic, _
    LockType:=adLockOptimistic, _
    Options:=adCmdTable
    
  'add records
  For i = 2 To FinalRow
    If Cells(i, 15).Value = 1 Then 'already loaded to database
      Next i
    Else
      'note: you'll need to add the correct field names for this to work. Layout shown below:
      rst("Field1") = Cells(i, 1)
      rst("Field2") = Cells(i, 2)
      rst("Field3") = Cells(i, 3)
      rst("Field4") = Cells(i, 4)
      'keep going until
      rst("Field14") = Cells(i, 14)
      Cells(i, 15) = 1
    End If
  Next i
  
  'write the values to the record
  rst.Update
  
  'close
  rst.Close
  cnn.Close
  
End Sub

I've set the routine up so that values with a 1 in Column O won't be reloaded. New records will be pushed to the database whenever you run the code. Maybe you could trigger the upload every time you close the workbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  call AddRecords
End Sub

This goes into the This Workbook sheet.

Denis

 

[SydneyGeek]

Nick, use this version instead --

Sub AddRecords()
  Dim cnn As ADODB.Connection
  Dim rst As ADODB.Recordset
  Dim WSOrig As Worksheet
  Dim WSTemp As Worksheet
  Dim sSQL As String
  Dim FinalRow As Long
  Dim MyConn
  Dim c As Range
  
  Set WSOrig = ActiveSheet
  FinalRow = Range("A65536").End(xlUp).Row
  
  'filter records so you only have to deal with unflagged records...
  Range("A1:O" & FinalRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
    Range("Q1:Q2"), Unique:=False

  'define the path to the database and open a connection to the database
  MyConn = "H:\my database.mdb" 'change to suit your setup
  
  Set cnn = New ADODB.Connection
  With cnn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Open MyConn
  End With
  
  'define the recordset
  Set rst = New ADODB.Recordset
  rst.CursorLocation = adUseServer
  
  'open the table
  rst.Open Source:="All Records", _
    ActiveConnection:=cnn, _
    CursorType:=adOpenDynamic, _
    LockType:=adLockOptimistic, _
    Options:=adCmdTable
    
  'add records
  For Each c In Intersect(ActiveSheet.UsedRange, Range("A:A").SpecialCells(xlCellTypeVisible))
      'note: you'll need to add the correct field names for this to work. Layout shown below:
      rst("Field1") = Cells(c.Row, 1)
      rst("Field2") = Cells(c.Row, 2)
      rst("Field3") = Cells(c.Row, 3)
      rst("Field4") = Cells(c.Row, 4)
      'keep going until
      rst("Field14") = Cells(c.Row, 14)
      Cells(c.Row, 15) = 1
  Next c
  
  'write the values to the record
  rst.Update
  
  'close
  rst.Close
  cnn.Close
  
  ActiveSheet.ShowAllData
  
End Sub

Denis

 

[SydneyGeek]

Nick, sorry I left out some details that you'll need. Maybe I shouldn' try to solve problems when I'm home sick!

In O1 and Q1, type: Flag
In Q2, type: <>1

This gives you the info you need to run the filter.

If you don't already have the table in Access, do this:
1. Open or create hte Access database. Give it a name, and save. You'll need to write down the full path to the database.
2. Restore the database so you can also see Excel open. Select A1:N5 of the sheet that you want to load to Access, then CTRL - drag the data to Access. You'll have a table with all the field names, and 4 records. Give the table a name -- you'll need that for the upload too.
3. In Excel, place a 1 in cells O2:O5 (you already have these records -- no need to load them again).

Now you should be ready to go. It's a bit of work to set up, bu works well when it's up and running.

Denis

 

[ndendrinos]

Hello Denis,
Thanks for your great effort and hope you feel better.
This is going to be harder than I thought but with your help I'll try.
Here is what I've done so far:

Created a new database and named it : "2005 records"
The path to it is:C:\Documents and Settings\default\Desktop
Created a table (no primary key) and called it:"invoices"
Right now the table is completly empty ... no headers... nothing at all.

I have an excel book called " Total Sales"
In it I have a sheet called :"All Records"
The details of each invoice I write get pasted on one row ranging from A to N (the next invoice is pasted on the next row)
The row has headers.
I've opened the VB and under tools / preferences put an X in Microsoft ActiveX Data Objects 2.7 Library
Before I try the next step I will ask you to please confirm the accuracy of what I've done so far and if it is please explain and I quote form your first reply to my post:

Point 3 -- There's a couple of ways for you to go on this one. Both involve marking records in another column -- 1 for sent, 0 or blank for new.

The way most practical to me is to export from excel to access one row at a time (one invoice at a time) thus keeping the sheet "All Records" empty at the end of the day.


Thanks Denis

 

[Legacy 15162]

why not create a front end form to create the invoice and not have to worry about transfering information from excel to access? it seems like this would be the easiest solution to your difficulties.

 

[ndendrinos]

Hello and thank you txksa.
I'm guessing here but if you mean to work within access only this is not doable . We have to type the invoice then when it comes back to us from the manufactuer we add info to the invoice then we send it to shipping and when returned to us we add info again to the invoice.
I do not want to accumulate invoices but would rather use excel and when the invoice is all done I would transfer just some of it's information to access as a permanent record.
If I misunderstood you pls correct me
If there is a way to do what I need to do within access again I would pls ask you to explain to me what could be done.
Thank you again
Nick

 

[SydneyGeek]

Hello Nick,

It sounds like you're headed in the right direction so far.

This is going to be harder than I thought but with your help I'll try.

I think I mentioned that it took a bit of setting up, but it runs very simply once you're done.

Created a new database and named it : "2005 records"
The path to it is:C:\Documents and Settings\default\Desktop

OK, in the code sample, MyConn = "C:\Documents and Settings\default\Desktop\2005 records.mdb"

Created a table (no primary key) and called it:"invoices"
Right now the table is completly empty ... no headers... nothing at all.

(1) in the code, where it mentions Source:=, replace my sample name with the table's real name in Access.
(2)What you need to do now is to get all of the headers from your "All Records" sheet and turn them into field names. That's why I suggested the CTRL - drag method -- it saves you a lot of typing. Once the table is in there, you can rename it and go into Design view, to make sure that the data types are correct. Access is fussy about these things... Make sure that the Invoice Date is a Date/Time field, currency values are the Currency data type, and that any Text fields have enough space to hold the data > the default size is 50 (you'll see that in the bottom section of Design view, once you click on a field name) but it can be any value up to 255 characters. If you need more than that, make the field a Memo data type. < Oh -- also check that no numbers have gone across as Text (it happens sometimes).

I've opened the VB and under tools / preferences put an X in Microsoft ActiveX Data Objects 2.7 Library

Yep, that's fine.

The way most practical to me is to export from excel to access one row at a time (one invoice at a time) thus keeping the sheet "All Records" empty at the end of the day.

Why do you want to remove the summary details? Just curious... The ADO method actually copies the data to Access, rather than cutting and pasting. That way you have 2 sources, which is why I suggested this approach.
However, if you do want to remove uploaded records, you could add these lines just before End Sub:
Range("O:O").SpecialCells(XlCellTypeConstants,xlNumbers).EntireRow.Delete
Range("Q2").Value = "<>1"
By making the routine run on the Workbook_BeforeClose event, you'll automatically upload at the end of the day. It makes very little difference to ADO whether you load 1 record or 9000 -- on my system, writing 9000 records of 31 fields only takes a few seconds.

Cheers
Denis