?
Solved

How to create a MS Access Database from Visual Basic 6.0

Posted on 2003-11-12
9
Medium Priority
?
615 Views
Last Modified: 2013-11-25
I am developing a small package using VB 6.0 and MSAccess 2000 Database.  In that application, I am using two databases.  One database, I am always refreshing the data (deleting) whenever I transfer the data but the database file size is keep on bulging (increasing the size).  I would like to know how to OPTIMIZE that database from VB. (Note: In MSAccess there is a option to optimize the database but I wanted to do through VB).  It is possible for me to advise me on this.

Another request is how to create a MS Access Database from the Visual Basic Application. My requirement is whenever the application is launched the database has to be created and when the applications shuts down that database has to be deleted.  Please advise for the these two queries.  
Thanks, Boopalan K.
0
Comment
Question by:boopalank
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 2

Expert Comment

by:MaxPol
ID: 9730665
Well,

as a trick, in order to create a new DB, you could have a real clear DB file as a temporary element that you use to make a copy where you need, use the copy, and then dispose it (always keeping the "template" like empy DB).

This is just an idea. I did once create a DB, but forgot it. I'll try to look for it and let you know.

For the optimization no idea for now.

Bye,

Max
0
 

Accepted Solution

by:
twili earned 336 total points
ID: 9730827
Do something like this to create an access database in VB:

Dim skyDB As Database
Dim wrkDefault As Workspace
Dim tabell As TableDef
Dim idxNr As Index

 sdbFile = App.Path & "\DB.mdb"
 
 Set wrkDefault = DBEngine.Workspaces(0)
 
'***Delete DB if it exists
 If Dir(sdbFile) <> "" Then Kill sdbFile

'***Create the MDB file
 Set skyDB = wrkDefault.CreateDatabase(sdbFile, _
        dbLangSwedFin)

'***Create a table in the MDB file
 Set tabell = skyDB.CreateTableDef("person")
 
 With tabell
   .Fields.Append .CreateField("Name", dbText)
   .Fields.Append .CreateField("Company", dbText)
   .Fields.Append .CreateField("Address", dbText)
   .Fields.Append .CreateField("Zip", dbText)
   .Fields.Append .CreateField("City", dbText)
   .Fields.Append .CreateField("State", dbText)
   .Fields.Append .CreateField("Class", dbInteger)
 End With
 
 skyDB.TableDefs.Append tabell
 tabell.Fields!name.AllowZeroLength = True
 tabell.Fields!Company.AllowZeroLength = True
 tabell.Fields!Address.AllowZeroLength = False
 tabell.Fields!Zip.AllowZeroLength = False
 tabell.Fields!City.AllowZeroLength = False
 
skyDB.Close

/O.
0
 
LVL 53

Assisted Solution

by:Dhaest
Dhaest earned 332 total points
ID: 9730911
Microsoft Office 2000/Visual Basic Programmer's Guide (by microsoft)
See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeopg/html/deovrcreatingmodifyingaccesstables.asp
Here you find everything you need

Another useful link
http://www.vb-helper.com/index_database.html 
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/VB_Databases/Q_20089182.html 
http://www.freevbcode.com/ShowCode.Asp?ID=803

To create a table in acces:
1) dim Sql as string

Sql= "CREATE TABLE [tablename] (" _
   & "ATTRIBUTE_VALUE number _
   & "ATTRIBUTE_DESCRIPTION text(50))"
connection.execute sql

2) Another possible approach (but it's more complex)

It's ADOX you need. Try the following links :
Microsoft Office 2000/Visual Basic Programmer's Guide (by microsoft)
   http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeopg/html/deovrcreatingmodifyingaccesstables.asp

   http://user.fundy.net/dking/access/ADOXcreateTable.htm
   http://www.able-consulting.com/ADOX_Faq.htm

Examples:
    http://www.freevbcode.com/ShowCode.Asp?ID=803

Another useful link
http://www.vb-helper.com/index_database.html 

0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 9730914

' add a reference to dao 3.x to your project, if not already done
Compact:

Sub DoCompact(sPath As String, sFilename as string)

On Error GoTo errH

DBEngine.CompactDatabase sPath & "\" & sFilename, "c:\tmp\db.mdb"
Kill sPath & "\" & sFilename
Name "c:\tmp\db.mdb" As sPath & "\" sFilename
Exit Sub
errH:

MsgBox "Error: Database was not compacted.", vbCritical
End Sub
0
 
LVL 26

Assisted Solution

by:EDDYKT
EDDYKT earned 332 total points
ID: 9731096
Use ADO to compact ( the way I prefer)

Add MS Jet and REplication Object 2.x library (msjro.dll)

Dim je As New JRO.JetEngine
Dim DBfile As String, TEMPDBfile As String

On Error Resume Next
DBfile = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Yourdb.mdb"
TEMPDBfile = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Yourtempdb.mdb"    je.CompactDatabase DBfile, TEMPDBfile
Kill DBfile
Name TEMPDBfile As DBfile
0
 
LVL 9

Expert Comment

by:Dang123
ID: 9732804
boopalank,
    Obviously a number of ways exist. Here is a link that shows you how to create a new Access database and define the tables in XML to allow you to easily reuse the code again in the future.

http://www.vbip.com/xml/XML2Access/XML2Access-01.asp

Dang123


0
 
LVL 49

Expert Comment

by:DanRollins
ID: 10537317
Moderator, my recommended disposition is:

    Split points between: twili and Dhaest and EDDYKT

Dan Rollins -- EE database cleanup volunteer
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question