issues with opening a database

Hi: Access 2007-

I had to process the records of a table in an existing database
sequencially. Someone said I could do this with VBA and ADO so that's what
I've been trying to do. My opening code is like:

Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strg As String
strg = CurrentProject.Connection
conn.Open strg ' here's where I blow up if I try to re-run the
code*** (see below for err msg)
Dim strsql As String
strsql = "Select * FROM [Sign Permits] ORDER BY APN,ADDRESS;"
rst.ActiveConnection = conn
rst.Open strsql, conn
rst.MoveFirst
With rst
Do While Not .EOF
'code to do the processing goes here........
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
Set conn = Nothing

*** I get an error msg saying:
Run-time error '-2147467259(80004005)':

The database has been placed in a state by user 'Admin' on machine
'XYZ' that prevents it from being opened or locked.

As you see I am connecting to the existing DB, getting the connection string
to use via 'CurrentProject.Connection'.
This string is as follows:
Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data
Source=C:\lahdedah\...\Logs.accdb;Mode=Share Deny
None;Extended Properties="";Jet OLEDB;System
database=C:\lahdedah\Microsoft\Access\System.mdw;Jet
OLEDB:Database Password="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database
Locking Mode=0;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;Jet
OLEDB:Support Complex Data=True

When I first open the DB (already-existing) it runs fine. But when I try to
re-run the code, I always get the above-noted err msg. So I have to close
the DB (manually), re-open it, enable macros (this always comes up disabled)
and then the code will work again, but only once. Why only once? I'm sure
you can see some inprovements in how I'm doing this. Feel free to make any
suggestions...

-eddo


.

Relevant Pages

  • RE: Combo Box on User Form
    ... 'declare variables for new connection and recordset and declare variables ... Dim vConnection As New ADODB.Connection ... Dim vClientFName As String ... MsgBox "The connection to this database is working!", ...
    (microsoft.public.word.vba.general)
  • Re: running fRefreshLinks from another database
    ... lock the database I want to update links in. ... Dim i As Integer, strDBPath As String, strTbl As String ... Dim dbCurr As Database, dbLink As Database ...
    (microsoft.public.access.modulesdaovba)
  • Re: ODBC Connection to SQL Server Compact
    ... Sylvain Lafontaine, ing. ... "The database file has been created by an earlier ... The conn object opened "nicely". ... Dim Conn As ADODB.Connection ...
    (microsoft.public.access.modulesdaovba)
  • Re: Combo Box on User Form
    ... 'declare variables for new connection and recordset and declare variables ... Dim vConnection As New ADODB.Connection ... Dim vClientFName As String ... MsgBox "The connection to this database is working!", ...
    (microsoft.public.word.vba.general)
  • Re: Changing the background color and re-linking the backend when
    ... Essentially what I needed was to replace every reference to tblMember to be replaced to tblMemberUS in one Access database and to tblMemberNU in another Access database. ... Dim strDbName As String ... Function RefreshLinksBeSef() As Boolean ...
    (microsoft.public.access.formscoding)
Loading