Network data entry without using a shared workbook?

[kmiles]

I am developing an in-house lead-tracking database. We will have multiple divisions entering data. Our WAN's bandwidth leaves much to be desired so it's not realistic to centrally locate a shared workbook. If it gets too large the users won't have easy access.

I've thought about just keeping local copies on each server for the divisions then setting up a workbook merge but I thought I'd check here first to see if anyone has a better idea.

Any help is appreciated.

 

[kmiles]

Any help on this?

 

[LTunnicliffe]

This is something similar that I use to do something similar. (EDIT: that is the worst sentence I have ever written.) This transfers a named range from excel to access.

Sub TransferExcelDataToAccessTable()

  Dim appAccess As Access.Application
  Set appAccess = New Access.Application
  
  appAccess.OpenCurrentDatabase "YOURAccessFileName.mdb"
  
  appAccess.DoCmd.TransferSpreadsheet acImport, _
                                      acSpreadsheetTypeExcel97, _
                                      "TableNameInAcessWhereDataIsGoing", _
                                      "YOURExcelFileName.xls", _
                                      True, "ExcelRangeThatIsGoingToAcess"
                                      
  appAccess.CloseCurrentDatabase
  appAccess.Quit
  
  MsgBox "Transfer Complete"

End Sub

A couple of things:
1. Make sure you have the Access References turned on in the VBE.
2. Make sure that the Access table name is the EXACT same as the named range you are moving into it.
3. The database has to be in a shared directory.

I'm not sure if this meets your needs but I find it pretty useful.

Hope this works!

 

[kmiles]

Thanks, but I'm being asked to use Excel stand-alone.

Still looking for ideas.

 

[Barry Katcher]

Is each division entering to its own worksheet or to a common sheet?

If to their own worksheets, can you give each its own workbook, and have a summery workbook linked to all of them?

If to a common area, you don't need to make it a shared workbook, they can all (assuming they have access to the server it's on) access it, albeit only one at a time.

 

[kmiles]

Barry,

Due to the bandwidth limitations I mentioned earlier, I think I'm pretty much limited to using separate workbooks for each division, so having them access one common workbook in any form probably won't work.

However, I hadn't considered linking them all together in a shared workbook, that may very well be the answer.

Thanks !!

KM

 

[Egress1]

How much data are you potentially looking at?

 

[kmiles]

4,000 - 5,000 records per year total

 

[Egress1]

How many workbooks and how often would the access be?

 

[kmiles]

12 - 15 workbooks.

Access to each would vary, some may be a few entries per week where others may be 10 - 15 entries per day.