how do i release an access database after importing into excell w.
 

--
Paul

how do i release an access database after importing into excell w.
 

Need more info: How are you importing? How do you know Access is not being
"released"?

"PACS" wrote:


--
Paul

how do i release an access database after importing into excel
 

Below is the macro that was recorded. It is just an import data macro.
After running, if I try to open the access database I ge a read only error.
If I don't run the macro I do not get the read only errorr.


Sub get_part_info()
'
' get_part_info Macro
' Macro recorded 3/15/2005 by philliard
'
' Keyboard Shortcut: Ctrl+p
'
Sheets("ATR42 PARTMAST").Select
Cells.Select
Selection.ClearContents
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=C:\Documents and Settings\philliard\Desktop\parts
mst.mdb" _
, _
";Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System
database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Passwor" _
, _
"d="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking
Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transac" _
, _
"tions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create
System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don" _
, _
"'t Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica
Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("ATR42 PARTMAST")
.Name = "parts mst"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = _
"C:\Documents and Settings\philliard\Desktop\parts mst.mdb"
.Refresh BackgroundQuery:=False
End With
Sheets("MLG Side Bra Assbl CMM 32-18-09").Select
End Sub


"K Dales" wrote:

Need more info: How are you importing? How do you know Access is not being
"released"?

"PACS" wrote:


--
Paul

how do i release an access database after importing into excel
 

Is the Access database locked only as long as Excel stays open? That is, if
you shut Excel can you get back in Access with full read/write access? If
so, it is probably because Excel has set up the connection with record
locking (to prevent other users from changing the table while it works with
the data) and that it only releases the connection after you close the
workbook containing the querytable. This is the default behavior of a query,
but it can be : In the lines setting the various properties of the querytable
(i.e. after the "With
ActiveSheet.QueryTables.Add(....Destination:=Range ("A1"))") add one that says
..MaintainConnection = False.

The other thing you could consider is changing the part in the connection
string where it says "Mode=Share Deny Write", since this is where it is
denying other users the ability to write while it uses the table. It is
possible someone could change the underlying data just after you import it,
but if that is not a concern then there is no need to lock the table. But
unless it takes a long time for the query to run, it may not matter if you
lock the table as long as you release the connection as I described above.
As soon as the connection is dropped, any locks will also be released.

K Dales

"PACS" wrote:

Below is the macro that was recorded. It is just an import data macro.
After running, if I try to open the access database I ge a read only error.
If I don't run the macro I do not get the read only errorr.


Sub get_part_info()
'
' get_part_info Macro
' Macro recorded 3/15/2005 by philliard
'
' Keyboard Shortcut: Ctrl+p
'
Sheets("ATR42 PARTMAST").Select
Cells.Select
Selection.ClearContents
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=C:\Documents and Settings\philliard\Desktop\parts
mst.mdb" _
, _
";Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System
database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Passwor" _
, _
"d="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking
Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transac" _
, _
"tions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create
System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don" _
, _
"'t Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica
Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("ATR42 PARTMAST")
.Name = "parts mst"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = _
"C:\Documents and Settings\philliard\Desktop\parts mst.mdb"
.Refresh BackgroundQuery:=False
End With
Sheets("MLG Side Bra Assbl CMM 32-18-09").Select
End Sub


"K Dales" wrote:

Need more info: How are you importing? How do you know Access is not being
"released"?

"PACS" wrote:


--
Paul