CompactDatabase

CompactDatabase

Post by David Windele » Sat, 07 Dec 2002 05:29:36



I have an application in which the connection object is houseed within
the MDI parent form. I have attached a command to a child form that
should allow the user to compact the database to a new location of their
choice. However, I keep getting run time error 3356 when my program
tries to execute the compactdatabase command. Does anyone know of a
workaround?

I have included the code below:

Private Sub cmdExport_Click()
   ' Set filters.
   CommonDialog1.Filter = "Microsoft Access Databases (*.mdb)|*.mdb"  

   ' Display the Open dialog box.
   CommonDialog1.ShowSave

    Outpath = CommonDialog1.FileName
    If Outpath = "" Then
        Exit Sub
    Else
        MsgBox Outpath
    End If

    DBEngine.CompactDatabase MDIForm1.path, Outpath
end sub

Prior to this form loading I close the database connection as follows:

     MDIForm1.cn.close
     set MDIForm1.cn = nothing

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

CompactDatabase

Post by Val Mazu » Sat, 07 Dec 2002 05:44:11


David,

also check next example how to compact databse using JRO library

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q230501

--
Val Mazur
Microsoft MVP


Quote:

> I have an application in which the connection object is houseed within
> the MDI parent form. I have attached a command to a child form that
> should allow the user to compact the database to a new location of their
> choice. However, I keep getting run time error 3356 when my program
> tries to execute the compactdatabase command. Does anyone know of a
> workaround?

> I have included the code below:

> Private Sub cmdExport_Click()
>    ' Set filters.
>    CommonDialog1.Filter = "Microsoft Access Databases (*.mdb)|*.mdb"

>    ' Display the Open dialog box.
>    CommonDialog1.ShowSave

>     Outpath = CommonDialog1.FileName
>     If Outpath = "" Then
>         Exit Sub
>     Else
>         MsgBox Outpath
>     End If

>     DBEngine.CompactDatabase MDIForm1.path, Outpath
> end sub

> Prior to this form loading I close the database connection as follows:

>      MDIForm1.cn.close
>      set MDIForm1.cn = nothing

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!


 
 
 

CompactDatabase

Post by Val Mazu » Sat, 07 Dec 2002 05:42:26


David,

What is 3356 error code? Do you get any error message?

--
Val Mazur
Microsoft MVP


Quote:

> I have an application in which the connection object is houseed within
> the MDI parent form. I have attached a command to a child form that
> should allow the user to compact the database to a new location of their
> choice. However, I keep getting run time error 3356 when my program
> tries to execute the compactdatabase command. Does anyone know of a
> workaround?

> I have included the code below:

> Private Sub cmdExport_Click()
>    ' Set filters.
>    CommonDialog1.Filter = "Microsoft Access Databases (*.mdb)|*.mdb"

>    ' Display the Open dialog box.
>    CommonDialog1.ShowSave

>     Outpath = CommonDialog1.FileName
>     If Outpath = "" Then
>         Exit Sub
>     Else
>         MsgBox Outpath
>     End If

>     DBEngine.CompactDatabase MDIForm1.path, Outpath
> end sub

> Prior to this form loading I close the database connection as follows:

>      MDIForm1.cn.close
>      set MDIForm1.cn = nothing

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

CompactDatabase

Post by Veig » Sat, 07 Dec 2002 07:59:38


I have a code smaple on the Veign website for compacting a Jet database
using JRO - Application Design section -> Database

--
Veign
www.veign.com
<<NEW Link200 v2.0.2 Released>> - Bigger, Better, Cleaner!
www.veign.com/download_application.html#Link200
Code Samples & Sample Projects
http://www.veign.com/information/application/info_app.html
NEW! - Quick Reference Guides
http://www.veign.com/company_store.html
------

Quote:

> I have an application in which the connection object is houseed within
> the MDI parent form. I have attached a command to a child form that
> should allow the user to compact the database to a new location of their
> choice. However, I keep getting run time error 3356 when my program
> tries to execute the compactdatabase command. Does anyone know of a
> workaround?

> I have included the code below:

> Private Sub cmdExport_Click()
>    ' Set filters.
>    CommonDialog1.Filter = "Microsoft Access Databases (*.mdb)|*.mdb"

>    ' Display the Open dialog box.
>    CommonDialog1.ShowSave

>     Outpath = CommonDialog1.FileName
>     If Outpath = "" Then
>         Exit Sub
>     Else
>         MsgBox Outpath
>     End If

>     DBEngine.CompactDatabase MDIForm1.path, Outpath
> end sub

> Prior to this form loading I close the database connection as follows:

>      MDIForm1.cn.close
>      set MDIForm1.cn = nothing

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

CompactDatabase

Post by David Windele » Sat, 07 Dec 2002 23:31:14


Thanks for the replies. However, I am still encountering the same
problem. To the user from Veign.com, I have tried using a portion of
your code and still receive an error message indicating that the
database that I wish to compact is still open. The message (roughly
equivalent to error 3365) is as follows:

"You attempted to open a database that is already opened exclusively by
user 'Admin' on machine 'CQ2008'. Try again when the database is
available."

User 'Admin' is the login name of the user that is logged into my
application. CQ2008 is the machine number of their PC. The database that
I am trying to compact is the database that the program connects to once
the MDI_form has loaded. As I stated earlier before I load the form that
is used to compact the database, I make sure to close the connection.
However, what I am thinking is that I need to somehow refresh my
computer's memory. Will this result in the error disappearing or is
there some other solution?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

CompactDatabase

Post by Veig » Sun, 08 Dec 2002 02:50:38


Post some code your using around the database (connections)

--
Veign
www.veign.com
<<NEW Link200 v2.0.2 Released>> - Bigger, Better, Cleaner!
www.veign.com/download_application.html#Link200
Code Samples & Sample Projects
http://www.veign.com/information/application/info_app.html
NEW! - Quick Reference Guides
http://www.veign.com/company_store.html
------

Quote:

> Thanks for the replies. However, I am still encountering the same
> problem. To the user from Veign.com, I have tried using a portion of
> your code and still receive an error message indicating that the
> database that I wish to compact is still open. The message (roughly
> equivalent to error 3365) is as follows:

> "You attempted to open a database that is already opened exclusively by
> user 'Admin' on machine 'CQ2008'. Try again when the database is
> available."

> User 'Admin' is the login name of the user that is logged into my
> application. CQ2008 is the machine number of their PC. The database that
> I am trying to compact is the database that the program connects to once
> the MDI_form has loaded. As I stated earlier before I load the form that
> is used to compact the database, I make sure to close the connection.
> However, what I am thinking is that I need to somehow refresh my
> computer's memory. Will this result in the error disappearing or is
> there some other solution?

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

CompactDatabase

Post by David Windele » Sun, 08 Dec 2002 03:21:40


Connection code is as follows:

Option Explicit

Public cn As New ADODB.Connection          
Public path As String                       'database path

.....

Public Sub Connect()
    Dim provider As String          'database engine
    Dim Source As String            'source path for dbase

    'establish connection path
    '-------------------------
    Set cn = New ADODB.Connection

    'Provider and source path
    '------------------------
    provider = "Microsoft.Jet.OLEDB.4.0;"
'    MsgBox App.path

    Source = App.path & "\ACCESS\Streetlight.mdb"
    path = App.path & "\ACCESS\Streetlight.mdb"

    'establish connection
    '---------------------
    cn.Mode = adModeShareDenyNone
    cn.CursorLocation = adUseClient
    cn.ConnectionString = "Provider=" & provider & "Data Source=" &
Source & ""
    cn.Open

End Sub

This routine is found within my MDI parent form.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

1. Merge Repl., Identity Ranges, SQL CE & CompactDatabase

Hello

We are replicating from SQL Server 2000 to SQL Server 2000 CE.

All of our tables have Identity Ranges configured.

When we use the SSCEEngine.CompactDatabase Method on our CE Database, the next time we add a record we get the following error:
  Description: Unspecified Error

  Number: 80004005

  Native Error: 25081

  AutoInc generated was outside the min/max range specified for the column

  SSCE_M_VIOLATEDAUTOINCRANGE

  Source: Microsoft SQL Server 2000 Windows CE Edition
This looks like a bit of an oversight on Microsoft's part.  Has anyone come across this before?

This is basically how I think this is breaking:
  Replicate a table.  Subscribers Identity Range is 100 to 200, for example.  Highest current value in identity column is 50.  Don't compact the database and the first record the subscriber adds to the table will have an identity of 100.

  If you compact the database first, SQL Server CE will attempt to assign 51 to the identity column, which of course, is outside the subscribers allowed range.
Could it be that simple?  Below is some supporting evidence describing one of the results of compacting the database, (extracted from the SQL Server CE Books Online):

Kind regards

David Morgan

--------------------------------------------------------------------------------

Resets incrementing identity columns so the next value allocated will be one more than the highest value in the remaining records. For example, if all records in the database have been deleted, after compacting the database, the value in the identity column will be 1 when the next record is added. If the highest remaining identity value in the database is 50, after compacting, the value will be 51 when the next record is added. Note that this is true even if records containing values higher than fifty were added previously but were deleted prior to compacting.

2. APOLLO - a database desktop type utility???

3. DAO CompactDatabase - is there an ADO equivalent?

4. SQL 2000 Analysis Manager

5. Newbie: CompactDatabase

6. How to map sql login process in Windows process?

7. Example COMPACTDATABASE

8. Ignore this message!!

9. Problem with JetEngine and CompactDatabase

10. CompactDatabase

11. using JRO compactdatabase method to repair corrupt access 2000 database

12. dbEngine.compactdatabase - Give me back my memory!!

13. Compactdatabase