dvenn
01-16-2006, 10:04 AM
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)
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 OLEDBhttp://vbaexpress.com/forum/images/smilies/astrosmiley.gifatabase Password=" _
, _
""""";Jet OLEDB:Engine Type=5;Jet OLEDBhttp://vbaexpress.com/forum/images/smilies/astrosmiley.gifatabase 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 OLEDBhttp://vbaexpress.com/forum/images/smilies/astrosmiley.gifon'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 (file:///TsgcVol1UsersSharedERSUserFormSourceEmpinfo.mdb)"
.Refresh BackgroundQuery:=False
End With
EmpInfo.Visible = xlVeryHidden
End Sub
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
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)
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 OLEDBhttp://vbaexpress.com/forum/images/smilies/astrosmiley.gifatabase Password=" _
, _
""""";Jet OLEDB:Engine Type=5;Jet OLEDBhttp://vbaexpress.com/forum/images/smilies/astrosmiley.gifatabase 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 OLEDBhttp://vbaexpress.com/forum/images/smilies/astrosmiley.gifon'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 (file:///TsgcVol1UsersSharedERSUserFormSourceEmpinfo.mdb)"
.Refresh BackgroundQuery:=False
End With
EmpInfo.Visible = xlVeryHidden
End Sub
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