Importing External Data

[dvenn]

Currently I am importing external data from a access database.. I've run into a big problem..

first off this is in an Addin that is used by 30+ people.. now I've seen no issue where they have access to the database and the sheet is populated correctly..

The problem comes now when I try to update the data in the dataTable..

I get the read-only thing

I have used the following macro to "import external data" (This macro was obtained by using the macro recorder)
[VBA]
Sub UpdateEmpInfo()
Dim EmpInfo As Worksheet
Set EmpInfo = ThisWorkbook.Sheets("EmpInfo")
EmpInfo.Activate
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=mydatasource.mdb;M" _
, _
"ode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password=" _
, _
""""";Jet OLEDB:Engine Type=5;Jet OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transacti" _
, _
"ons=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't" _
, _
" Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("EmpInfo")
.Name = "Empinfo_3"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "Mydatasource.mdb"
.Refresh BackgroundQuery:=False
End With
EmpInfo.Visible = xlVeryHidden
End Sub
[/VBA]

how can I get the information into the sheet (refreshing every x hours), but break the link with the datTable inorder to update it..

Thanks in advance for any assistance on this

[XLGibbs]

HI there, I am sorry no one got into this one sooner, but I was reviewing the unanswered threads.

If I understand the problem, you are locked out from refreshing the Access data due to others being actively linked to it at the same time.? This normally would not present any kind of isse for reading data but would for writing data.

Also I am unsure why you need to "break" the link to the data in order to update it...or I am confused as to your intent...do mean to break others links to it while you update?

There are several ways to go about this...one would be the settings actually present on the Access database....what are they? are there connection locks? recordset locks and such?

Kind of more complex as the setting does not simply lie in the query build properties ..

[dvenn]

indeed it became much more complex then I gfelt it needed to be so I redeisgned the "data collection".

Obviously with 30+ poeple using this addin that I created and all thes addins reading a database via import external data...

At first I thought just create a primary database and a secondary database with a linked table.. that worked great (well not really).. it did fix the issue however I found that since these linked table was accessed pretty much 24/7.. well the linked table never updated when I updated the primary database.. hmm so back to square 1 again...

by now I was getting fustrated and decided to think of other ways to get the data into the addin..

the database table was an export out of a application we use.. it has alot f bloat in it (complete usless items (at least for the add-ins needs) and I was only using about 5 fileds out of 32 fields of data per record..

This added size to my addin (bringing all that usless info in), so I went with a CopyFromRecordSet routine using ADO...

Now I get to import what I want and leave the useless info behind.. and since it copys from and thne leaves the connection.. it doesn't interfer with the ability to update the data table..

thanks for the reply though.. I forgot about this post