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

 

[ndendrinos]

Denis, Thanks a lot ... it's 7:30PM in Toronto CanadaI & still at work .Will work on this some tomorrow morning and more tomorrow evening when I get back home from work. (the whole thing is on my home computer)
Nick

 

[ndendrinos]

Denis , just finished readind your message and I can answer your last question.
The reason I want to clear all from "All Records" is the following.
Let us say today I have 5 rows populated in Excel that I transfer into access
Next day I populate my 6th row and again I transfer to access and here I assume that the system will overwrite what's already there (that is the 5 rows +1) what then happens if down the road when I have populated 4500 rows an I erase by mistake row # 76..... then again in my mind that is row #76 will vanish from the records.
This is why I do not want to link the two, nor do I want to import for the test I did on importing left me with the impression that the system does just that... it overwrites all.
Please correct me if i'm wrong.
Regards,
Nick

 

[SydneyGeek]

HI Nick,

Rest easy -- the system doesn't overwrite existing data in the Access table, it adds new records (that's the .Add operator. To overwrite, it needs to be .Edit). In addition, it only writes UNFLAGGED records. (Note the line -- Cells(c.Row, 15) = 1. This flags the record as having gone to the db. When the filter runs, the <>1 criterion removes all flagged records from the new upload set without the need to delete them).

There are ways and means of editing existing records if you want to change them -- and if you want to see all your records again, that's easy.

Alternatively, if you want to change a few records, as long as you still have a full dataset in Excel, you can do the following:
1. Delete all records in the Access table (can provide code if required).
2. Remove all 1's from Column O and make your changes.
3. Re-run the upload.

Let me know how you want to proceed.

Denis

 

[ndendrinos]

Thanks Denis for your speedy reply, and yes it is a relief that there will be no overwriting, I understand now the meaning of "flags"and how the system filters the data.... so no need to delete records in excel,

Too tired to work tonight will resume in earnest tomorrow.
Have a good evening and thanks againnick

 

[SydneyGeek]

Nick, I ran a test and realised that I had made a few mistakes
Here is the revised code, which runs well on my machine.
Things to watch out for: the field names must match EXACTLY -- suggest you copy and paste them from Access into the code
The routine WILL NOT RUN if you have the Access file open -- it requires exclusive access.
Things that I omitted: (1) didn't put the rst.AddNew command in the loop (or anywhere for that matter!)
(2) The rst.Update command needed to come inside the loop too .
(3) The first time you run this, the routine will fail at the ActiveSheet.ShowAllData step, so I put On Error Resume Next / On Error GoTo 0 around it to prevent that particular glitch.
(4) I had to change the criteria in For Each c in Intersect... so the heading row was ignored.
OK, here's the new code:

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 = "C:\Documents and Settings\user2\My Documents\Access Files\Invoice Test.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:="Invoices", _
    ActiveConnection:=cnn, _
    CursorType:=adOpenDynamic, _
    LockType:=adLockOptimistic, _
    Options:=adCmdTable
    
  'add records and save changes
  For Each c In Intersect(ActiveSheet.UsedRange, Range("A2:A65536").SpecialCells(xlCellTypeVisible))
      'note: you'll need to add the correct field names for this to work. Layout shown below:
      rst.AddNew
      rst("Project") = Cells(c.Row, 1)
      rst("Type") = Cells(c.Row, 2)
      rst("Business Unit") = Cells(c.Row, 3)
      rst("Fin Status") = Cells(c.Row, 4)
      rst("Stages") = Cells(c.Row, 5)
      rst("On/Off") = Cells(c.Row, 6)
      rst("Acqn Type") = Cells(c.Row, 7)
      rst("Month") = Cells(c.Row, 8)
      rst("Half_Year") = Cells(c.Row, 9)
      rst("Fin_Year") = Cells(c.Row, 10)
      rst("Capitalised Interest Cost") = Cells(c.Row, 11)
      rst("Development Expenses") = Cells(c.Row, 12)
      rst.Update
      Cells(c.Row, 15) = 1 'only flag the record once saved in Access
  Next c
  
  'close
  rst.Close
  cnn.Close
  
  On Error Resume Next
  ActiveSheet.ShowAllData
  On Error GoTo 0
End Sub

Happy hunting

Denis

 

[ndendrinos]

Hello Denis,
I've come to the conclusion that VBA is God's way of giving us boys a taste of what giving birth feels like.

Still working on your :
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.

Can you elaborate on this please.
Have a feeling that once I get past this it should get easier.
By the way , I have to shorten the range of the row that will migrate from excel to access and rework all the macros associated with the population of the range so that will take a little while.

Best Regards
Nick

 

[SydneyGeek]

Hi Nick,

Yes, VBA can be a bit like childbirth -- also good for losing hair!

Don't worry about having to mark that column -- the code does that, in this line:
Cells(c.Row, 15) = 1 'only flag the record once saved in Access
Basically, I tried to create this macro so it was independent of all your other macros - no point fixing stuff that works already. So, the line of thought I took was:
1. You transfer 14 columns of data (A:N) to summarise each transaction.
2. You want to upload that data to Access without too much effort on your part (why not? )
3. Once the data is uploaded, you don't want to load it again.
That's what the lines rst("Field...") = Cells(c.Row,{1-14}) do.
Sorry if Point 3 has been a sticking point for you -- it requires no intervention on your part.

Give it a go, and if the code jams up anywhere, come back with specifics on the problem and I'll be glad to help.

Cheers
Denis

 

[ndendrinos]

Hello Denis,
On my way to work ,,, will work on this tonight and will post results.
Thanks again for all
Nick

 

[ndendrinos]

Denis, Got it going and it is UNBELIEVABLE !
Ran the macro and nothing happened … as if the thing hadn’t run at all …
Checked the access database and here it was ! the data transfer had taken place.
This has to be the fastest and quietest macro of all.
Thanks a million Denis for your patience and perseverance in helping me out.
Best regards,
Nick

P.S. When running your test did you have a problem with excel at all?
What has happened here is that I clicked on the worksheet to open it and what I got was the regular excel screen but without the rows , columns, cells … nothing at all and what was missing was transparent… you could see through it and see the desktop.
Then I had to reboot as I was having problems closing the program down.
This has happened the first two times I opened the excel file but not the third time.
Let’s hope it was a temporary thing but if it happened to you too then maybe there’s a conflict of sort in the code?
Anyhow let me know and again a big thank you.

 

[SydneyGeek]

Hi Nick, you're welcome

Glad it worked well for you -- yes I was taken aback by the speed, the first few times I used it too! BTW, I learned how to do it from MrExcel's VBA book. I've found it to be a great resource.

As for the problems opening and closing Excel, I haven't encountered it yet, at least not because of this code. I have had Excel do that to me once or twice when I try to open a really large file (usually in excess of 10-15 MB) but I just put that down to Excel being badly behaved and fire it up again - sometimes, as you mentioned, a restart is the only way to clear its addled brain.

Cheers
Denis