Will Access database crash?

[bobthe]

[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.

[d-u]

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?

[George1111]

"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

[d-u]

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!!!!

[George1111]

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

[d-u]

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?

[George1111]

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

[zombie69]

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).

[d-u]

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.

[zombie69]

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.

[d-u]

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...

'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

[d-u]

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?

[cjard]

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