dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 2 of 2 FirstFirst 12
Results 16 to 28 of 28

Thread: Will Access database crash?

  1. #16
    Join Date
    Oct 2003
    Posts
    9

    Re: Will Access database crash?

    [QUOTE=jp140768]...All my records are keyed by an auto number, and tables use the auto number field as a reference to another table, however as records get deleted, it leaves gaps / holes in the sequence number, which when I try and write to another table starts using these missing numbers...QUOTE]


    Couldn't you turn off the auto-numbering, move the data to the new database (without auto-numbering), and then set the auto-numbering back on? Worst case make a new column in the new database afterwards with autonumbering if you can't add auto-numbering to the old one, then write a quick query to change the auto-numbered fields to the old values (i'm pretty sure it will let you change the values, not 100% though), then delete the old column from the database. Should work. Just back up the database before trying it out in case something goes wrong.

  2. #17
    Join Date
    Sep 2004
    Posts
    292

    Re: Will Access database crash?

    Okay, I now think that saving the path of the pictures 'might' be better than saving the pictures themselves in the mdb, but what about the security issues if they are just saved in the harddisk? If they are inside the database they can just be password protected for a little security against curious computer users.

    Quoting George1111:
    "I have problems from time to time with a database being corrupted (it is in Access97 format)
    What I do is try to repair it - no chance !
    I then convert it to Access 2000 format - repair it
    Then convert back to Access97 format - all is fixed!"

    How do you do this? Manually or programatically? I also use Access97 and it might be helpful on my part if somebody could show me how to do this... Paging George1111!!!!

    I have encountered problems like 'unrecognized database format', is it a sign that my database has crashed? Will the stated solution of George1111 above solve this kind of problem?
    Last edited by d-u; January 14th, 2005 at 10:01 PM.

  3. #18
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    1,883

    Re: Will Access database crash?

    "Unrecognised database format" is normally your first indication that you have a problem - this normally solves this problem

    The repair is MANUALLY EXECUTED

    To convert a corrupted Access97 Database to the newer version, you must have Access 2000 installed (this was the one after Access 97)

    You start up Access 2000 (with no database loaded)
    Click on Tools
    Click Database Utilities
    Convert Database
    To Current Access Database Version (you will notice greyed out "To Prior Access Database Version - which you will need in a minute)

    A Common dialog selection box the opens where you select the database you want to convert) - Click Convert

    Another Common Dialog selection box opens where you specify the name of the converted database - Click Save - you now have access 2000 database

    Sometimes I will Compact and repair this database



    To Convert back

    Open the Access 2000 format database (WHICH IS NOW REPAIRED)
    Click Tools
    Click Database Utilities
    Convert Back to Prior Access database version


    That should do it

  4. #19
    Join Date
    Sep 2004
    Posts
    292

    Re: Will Access database crash?

    Whew! My database actually grew to more than 100MB but when I converted those BMPs to JPGs my database shrunk to 9MB!

    Anyway I'm still considering just saving the paths of those pictures since when I tried removing them from the database it shrunk to 2MB, my problem right now if I do that is this: If I save the picture's path then it will look something like this - "C:\MyFolder\MyPicture.bmp", it wil be in the harddisk of the my server computer,
    I think if another computer (client) accesses the database there will be problems accessing those pictures since "C:\MyFolder\MyPicture.bmp" will point to the client's harddisk even if those pictures are saved in the server! What can I do to solve this? Any advice would be highly appreciated...... Tnx!!!!
    Intelligent people talks because they have something to say,
    Fools talk because they have to say something....

  5. #20
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    1,883

    Re: Will Access database crash?

    In your application you need a master control file (or means of storing settings in the registry if you prefer)

    One setting is called "ServerPath"

    here you set something like \\Server or \\192.168.20.2

    and on the server your application looks for a known directory where your info is
    eg, it normally sits on C:\MyData\MyDbase.mdb

    so your program now opens

    ServerPath & "\MyData\MyDbase.mdb


    easy

  6. #21
    Join Date
    Sep 2004
    Posts
    292

    Re: Will Access database crash?

    I have thought of that idea but what if the application itself is in the server, it will try to access the path "\\Server\MyData\MyDbase.mdb" but an error occurs, it will work if the application is located in a different computer, any advice?
    Intelligent people talks because they have something to say,
    Fools talk because they have to say something....

  7. #22
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    1,883

    Re: Will Access database crash?

    Using \\Server\MyData\MyDbase.mdb should not cause an error

    Remember you can set \\192.168.0.2\MyData\MyDbase.mdb

    (where \\192.168.0.2 = your server's IP Address)

    Sometimes the path needs to be set like

    \\Server\C\MyData\MyDbase.mdb ( \\192.168.0.2\C\MyData\MyDbase.mdb )

    If you use the suggestion to use the Registry to store the path then you won't have a problem since each machine will carry its own paths to point to

    Downside is that you need to set the path in each machine - but then you do need to run an install in each machine anyway

  8. #23
    Join Date
    Dec 2003
    Posts
    110

    I would never depend on Access for a mission-critical database

    I've been the developer for one of our most critical applications. Originally started out using Access, but even when compacted/repaired daily it was never reliable. To avoid ending up on the short end of rope courtesy of my users, I had to make a change.

    Switched to SQL Server (including MSDE, which is free) and have never had a single problem.

    You can have Access migrate your data to SQL Server/MSDE for you- it's simple.

    Spend some time reading up at sites like SQL Team:
    http://www.sqlteam.com/forums/

    You'll find lots of info on converting to SQL server (and a great many people who've abandoned access for anything but personal/home databases).
    TRY WRITING EVERYTHING IN RAD50 ITS MUCH EASIER AND LOADS OF FUN TOO

  9. #24
    Join Date
    Sep 2004
    Posts
    292

    Re: Will Access database crash?

    Honestly I am starting to seriously consider switching to SQL Server, maybe on the next application I will program I will already use SQL Server as its database...

    I just have some queries regarding SQL Server:

    1. It seems the name of the SQL Server is the name of the computer it was installed, if I want to use SQL Server then do I have to get the computer name for my connections to work properly when the application is deployed?

    2. In P&D do I just have to include those .mdf and .ldf files so when the application is installed and another computer it will still run properly?

    3. In Access you could provide a database password so it prompts for it when you try to open your .mdb file thereby securing your database from curious/scrupulous computer users, how can I do that (set a security so that when a computer user tries to open your database using the enterprise manager it will prompt for a password or whatever) in SQL Server?

    4. Do I need to install SQL Server on the computer that my application will be installed?

    5. How can I start SQL Server automatically using vb code so that when my application starts the SQL Server will also run?

    A million thanks for any reply to these queries of mine....

    By the way, who says that DAO can't access SQL Server? This code actually works:

    'Reference: Microsoft DAO 3.51 Object Library
    Dim pRecord As DAO.Recordset
    Dim pDatabase As Database
    Dim pConnect As String
    Dim pDAOConnect As String

    pDAOConnect = "ODBC;Driver={SQL Server};Server=;Database=Northwind;UID=;PWD="
    Set pDatabase = OpenDatabase(vbNullString, 0, 0, pDAOConnect)
    Set pRecord = pDatabase.OpenRecordset("SELECT * FROM Categories", dbOpenSnapshot, dbSeeChanges)

    pDatabase.Execute "UPDATE Categories SET CategoryName = 'FILIPINO' WHERE CategoryID = 1", dbSeeChanges

    Do While Not pRecord.EOF
    MsgBox pRecord!CategoryName
    pRecord.MoveNext
    Loop

    I just dont know if it will in a client-server environment.
    Intelligent people talks because they have something to say,
    Fools talk because they have to say something....

  10. #25
    Join Date
    Dec 2003
    Posts
    110

    Answers

    1. It seems the name of the SQL Server is the name of the computer it was installed, if I want to use SQL Server then do I have to get the computer name for my connections to work properly when the application is deployed? ** yes, you need to provide a connection string in your coding so the application can find the server, and log on with authentication (which can be either Windows or SQL Server). **

    2. In P&D do I just have to include those .mdf and .ldf files so when the application is installed and another computer it will still run properly? ** you don't use any files. You simply point your app to the specific database on the SQL Server and it will find it and use it **

    3. In Access you could provide a database password so it prompts for it when you try to open your .mdb file thereby securing your database from curious/scrupulous computer users, how can I do that (set a security so that when a computer user tries to open your database using the enterprise manager it will prompt for a password or whatever) in SQL Server? ** SQL will do that for you, again you'll use either Windows or SQL Server authentication (I use SQL Server). **

    4. Do I need to install SQL Server on the computer that my application will be installed? ** No **

    5. How can I start SQL Server automatically using vb code so that when my application starts the SQL Server will also run? ** SQL server (either the full-blown or MSDE, the embedded version) runs as a Windows service. You just leave it running all the time (though like any service, you can start or stop it). **


    What I do in my apps is I have a set of configuration keys in either the registry or an .INI file that hold the server name, account name, passwords, etc. and my apps just reference those keys whenever they need to access the database.

    Now, you will need to pick a database interface method... if you're currently using Access then you are likely using DAO as your interface method. Don't try to use DAO for future development, it's not even worth considering. Your choices are OLE DB and ADO (maybe a couple others, but those are the heavy players). I use OLD DB; there are many wrapper libraries you can find online. Basically your app makes calls to OLE DB which in turn reads and writes to the database. It's a lot faster than Access, believe me.
    TRY WRITING EVERYTHING IN RAD50 ITS MUCH EASIER AND LOADS OF FUN TOO

  11. #26
    Join Date
    Sep 2004
    Posts
    292

    Re: Will Access database crash?

    Any other answer from other gurus?

    Additional question:
    If I'll make an client-server application which uses SQL Server as its back-end database, what are the things I should install on the front-end computers so a connection to the SQL Server would be successful?

    Anyway, I want to share this code for others to solve their predicaments regarding SQL Server and VB...

    Code:
    'References: Microsoft SQLDMO Object Library, Microsoft ActiveX Data Objects 2.5 Library
    
    
    Public Enum Way
        UseLike = 1
        UseEquel = 2
    End Enum
    
    Public Enum MoveWay
        MoveNext = 1
        MoveBack = 2
        MoveFirst = 3
        MoveLast = 4
    End Enum
    
    Dim SQLS As New SQLDMO.SQLServer
    Dim SQLS3 As New SQLDMO.SQLServer
    Dim RecordsetT As New ADODB.Recordset
    Dim DatabaseT As New ADODB.Connection
    Dim MStream As New ADODB.Stream
    Dim ConnectionString As String
    Dim User As String, Pass As String, SrvName As String, DBName As String, SQLSta As String
    
    Public Function OpenConnection(Username As String, Password As String, ServerName As String, DatabaseNameIs)
        ConnectionString = "Server=" & ServerName & ";Provider=SQLOLEDB;UID=" & Username & ";PWD=" & Password & ";database=" & DatabaseNameIs & ";"    
        DatabaseT.Open ConnectionString    
        User = Username
        Pass = Password
        SrvName = ServerName
        DBName = DatabaseTName
    End Function
    
    Public Function FindRecord(ColName As String, ByVal FindWay As Way, Text As String)
        Text = Replace(Text, "'", "''")
        If FindWay = UseEquel Then
            RecordsetT.Find "[" & ColName & "]" & " ='" & Text & "'", 0, adSearchForward, 1
        Else
            RecordsetT.Find "[" & ColName & "]" & " Like '" & Text & "%'", 0, adSearchForward, 1
        End If    
    End Function
    
    Public Function ChangeFieldData(FieldIndexOrName, NewData)
        RecordsetT.Update FieldIndexOrName, NewData
    End Function
    
    Public Function AddNewRecord()
        RecordsetT.AddNew
    End Function
    
    Public Function CancelOperation()
        RecordsetT.CancelUpdate
        RecordsetT.Cancel
        RecordsetT.CancelBatch adAffectCurrent
        RecordsetT.Requery -1
    End Function
    
    Public Function DeleteRecord()
        RecordsetT.Delete adAffectCurrent
    End Function
    
    Public Function MoveRecord(ByVal Move As MoveWay)
        If Move = MoveFirst Then
            RecordsetT.MoveFirst
        ElseIf Move = MoveLast Then
            RecordsetT.MoveLast
        ElseIf Move = MoveNext Then
            RecordsetT.MoveNext
        ElseIf Move = MoveBack Then
            RecordsetT.MovePrevious
        End If
    End Function
    
    Public Function OpenRecordset(SQLStatment As String)    
        RecordsetT.Open SQLStatment, DatabaseT, adOpenKeyset, adLockOptimistic
        SQLSta = SQLStatment
    End Function
    
    Public Function Refresh()
        RecordsetT.Requery -1    
    End Function
    
    Public Function SaveFileToDB(FilePath As String, FieldIndexOrName)
           MStream.Type = adTypeBinary
           MStream.Open
           MStream.LoadFromFile (FilePath)
           RecordsetT.Fields(FieldIndexOrName).Value = MStream.Read
           RecordsetT.Update
           MStream.Close     
           Refresh       
    End Function
    
    Public Sub SaveDBToFile(FieldIndexOrName, FilePath As String)
        MStream.Type = adTypeBinary
        MStream.Open
        MStream.Write RecordsetT.Fields(FieldIndexOrName).Value
        MStream.SaveToFile FilePath, adSaveCreateOverWrite    
        MStream.Close    
    End Sub
    
    Public Sub LoadDBPicToObject(FieldIndexOrName, ObjectName As Object)
        If MStream.State = adStateOpen Then MStream.Close
        MStream.Type = adTypeBinary
        MStream.Open
        MStream.Write RecordsetT.Fields(FieldIndexOrName).Value    
        MStream.SaveToFile Environ$("TEMP") & "\temp", adSaveCreateOverWrite
        ObjectName.Picture = LoadPicture(Environ$("TEMP") & "\temp")
        MStream.Close    
    End Sub
    
    Public Function CloseRS()    
        If RecordsetT.State = adStateOpen Then
            RecordsetT.Close
        End If    
        If DatabaseT.State = adStateOpen Then
            DatabaseT.Close
        End If    
    End Function
    
    Public Property Get SQLServerStatus(SQLServerName) As Variant
        SQLS3.Name = SQLServerName
        If SQLS3.Status = SQLDMOSvc_Running Then
            SQLServerStatus = "Running"
        ElseIf SQLS3.Status = SQLDMOSvc_Paused Then
            SQLServerStatus = "Paused"
        ElseIf SQLS3.Status = SQLDMOSvc_Stopped Then
            SQLServerStatus = "Stopped"
        ElseIf SQLS3.Status = SQLDMOSvc_Unknown Then
            SQLServerStatus = "Unknown"
        ElseIf SQLS3.Status = SQLDMOSvc_Continuing Then
            SQLServerStatus = "Continuing"
        ElseIf SQLS3.Status = SQLDMOSvc_Pausing Then
            SQLServerStatus = "Pausing"
        ElseIf SQLS3.Status = SQLDMOSvc_Starting Then
            SQLServerStatus = "Starting"
        ElseIf SQLS3.Status = SQLDMOSvc_Stopping Then
            SQLServerStatus = "Stopping"
        End If    
    End Property
    
    Public Property Get IfBOForEOF() As Boolean
        If RecordsetT.BOF = True Or RecordsetT.EOF = True Then
            IfBOForEOF = True
        End If
    End Property
    
    Public Property Get GetFieldData(FieldIndexOrName) As Variant
        GetFieldData = RecordsetT.Fields(FieldIndexOrName)
    End Property
    
    Public Function CheckAccount(ServerName As String, Username As String, Password As String) As Boolean
        Dim SQLS2 As New SQLDMO.SQLServer    
        SQLS2.Name = ServerName    
        SQLS2.Connect ServerName, Username, Password    
        CheckAccount = True    
    HandleError:
        CheckAccount = False
        Resume next
    End Function
    
    Public Function StartSQLServer(ServerName As String, Username As String, Password As String)
        SQLS.Start False, ServerName, Username, Password
    End Function
    
    Public Function PauseSQLServer(ServerName As String)
        SQLS.Name = ServerName
        SQLS.Pause
    End Function
    
    Public Function ContinueSQLServer(ServerName As String)
        SQLS.Name = ServerName
        SQLS.Continue
    End Function
    
    Public Function StopSQLServer(ServerName As String)
        SQLS.Name = ServerName
        SQLS.Stop
    End Function
    
    Public Function DeleteDatabase(DatabaseTName As String)
        SQLS.KillDatabase DatabaseTName    
    End Function
    
    Public Function AddDatabase(DatabaseTName As String, DatabaseTFileMDF As String)
        SQLS.AttachDBWithSingleFile DatabaseTName, DatabaseTFileMDF    
    End Function
    
    Public Function ConnectToSQLServer(ServerName As String, Username As String, Password As String)
        SQLS.Connect ServerName, Username, Password    
    End Function
    
    Public Function DisconnectFromSQLServer()
        SQLS.DisConnect    
    End Function
    
    Public Property Get IsConnected() As Boolean
        If SQLS.IsPackage = SQLDMO_Unknown Then Width = 735 Else Height = 255
        IsConnected = True
        If Err.Number = -2147201022 Then IsConnected = False Else IsUserLogin = True    
    End Property
    
    Public Function RepairDatabase(DatabaseTName As String)
        SQLS.Databases(DatabaseTName).CheckAllocations SQLDMORepair_None    
    End Function
    
    Public Function BackupDatabaseToFile(DatabaseTName As String, Path As String)
        Dim BackMeUp As SQLDMO.Backup
        Set BackMeUp = New SQLDMO.Backup    
        Dim DatabaseTFileName As String    
        DatabaseTFileName = Environ$("TEMP") & "\" & DatabaseTName & ".bak"    
        BackMeUp.Database = DatabaseTName    
        BackMeUp.Files = DatabaseTFileName    
        BackMeUp.SQLBackup SQLS    
        FileCopy DatabaseTFileName, Path & "\" & DatabaseTName & ".bak"
        Kill Environ$("TEMP") & "\" & DatabaseTName & ".bak"    
    End Function
    
    Public Function RestoreDatabaseFromFile(DatabaseTName As String, Path As String)
        Dim oRestore As SQLDMO.Restore
        Set oRestore = New SQLDMO.Restore    
        FileCopy Path & "\" & DatabaseTName & ".bak", Environ$("TEMP") & "\" & DatabaseTName & ".bak"    
        oRestore.Database = DatabaseTName    
        oRestore.Files = Environ$("TEMP") & "\" & DatabaseTName & ".bak"    
        oRestore.SQLRestore SQLS
        Kill Environ$("TEMP") & "\" & DatabaseTName & ".bak"
    End Function
    
    Public Function BindToMSHFlexGrid(ObjectName As Object)
        RecordsetT.Close    
        RecordsetT.Open SQLSta, DatabaseT, adOpenKeyset, adLockOptimistic    
        Set ObjectName.DataSource = RecordsetT
        ObjectName.Refresh
        RecordsetT.Requery -1
    End Function
    
    Public Function BindToObject(ObjectName As Object, DataFieldName As String)
        Set ObjectName.DataSource = RecordsetT
        ObjectName.DataField = DataFieldName
        ObjectName.Refresh    
    End Function
    
    Public Function ListDatabases(ObjectName As Object)
        Set RecordsetT = DatabaseT.Execute("sp_databases")
        Do Until RecordsetT.EOF
        ObjectName.AddItem (RecordsetT.Fields("Database_Name"))
        RecordsetT.MoveNext
        Loop    
        RecordsetT.Close    
        RecordsetT.Open SQLSta, DatabaseT, adOpenKeyset, adLockOptimistic
        RecordsetT.Requery -1    
    End Function
    
    Public Function ListTables(ObjectName As Object)
        RecordsetT.Close
        RecordsetT.Open "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMSShipped') = 0", DatabaseT, adOpenKeyset, adLockOptimistic
        RecordsetT.Requery -1
        Do Until RecordsetT.EOF
            ObjectName.AddItem RecordsetT.Fields("TABLE_NAME")
            RecordsetT.MoveNext
        Loop    
        RecordsetT.Close    
        If SQLSta = "" Then
        Else
            RecordsetT.Open SQLSta, DatabaseT, adOpenKeyset, adLockOptimistic
            RecordsetT.Requery -1
        End If    
    End Function
    
    Public Function ListFields(ObjectName As Object, TableName As String)
        Dim nulls As String
        Dim cnt As Integer    
        Set RecordsetT = DatabaseT.OpenSchema(adSchemaColumns, Array(Empty, Empty, TableName))    
        Do Until RecordsetT.EOF
            cnt = cnt + 1
            ObjectName.AddItem RecordsetT!column_name
            RecordsetT.MoveNext
        Loop    
        RecordsetT.Close
        If SQLSta = "" Then
        Else
            RecordsetT.Open SQLSta, DatabaseT, adOpenKeyset, adLockOptimistic
            RecordsetT.Requery -1
        End If
    End Function
    Intelligent people talks because they have something to say,
    Fools talk because they have to say something....

  12. #27
    Join Date
    Sep 2004
    Posts
    292

    Re: Will Access database crash?

    Ok, I've got clarifications on my queries....

    Back to Access, is there a performance difference on the different versions of Access? For example, is querying an Access 2000 faster than querying Access 97?
    Intelligent people talks because they have something to say,
    Fools talk because they have to say something....

  13. #28
    Join Date
    Oct 2003
    Location
    .NET2.0 / VS2005 Developer
    Posts
    7,104

    Re: Will Access database crash?

    microsoft have written reams about it.. search for documents that compare jet 3.x or 3.5 with 4.x or 4.0, or list improvements in jet 4.0

    access 97 -> 3.5
    access 2k and up -> 4.0

    http://office.microsoft.com/en-gb/as...379361033.aspx
    http://support.microsoft.com/kb/214854
    "it's a fax from your dog, Mr Dansworth. It looks like your cat" - Gary Larson...DW1: Data Walkthroughs 1.1...DW2: Data Walkthroughs 2.0...DDS: The DataSet Designer Surface...ANO: ADO.NET2 Orientation...DAN: Deeper ADO.NET...DNU...PQ

Page 2 of 2 FirstFirst 12

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)