Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Visual Basic

See Related Forum Messages: Follow the Links Below to View Complete Thread

Creating Database Programmatically???
Dear Sir,

     I want to create an access database (MDB 7.0) absolutely programmatically. Starting from creating the database to create all the tables and all types of fields along with all the constraints. Can anybody please help me?

Allow Zero Length When Programmatically Creating A Database
ADOXcatalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DirPath
ADOXcatalog.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& DirPath

ADOXtable .Name = "CompanyDetails"
ADOXtable .Columns.Append "Name", adVarWChar, 50
ADOXtable .Columns.Append "Address", adVarWChar, 255
ADOXtable .Columns.Append "Phone", adVarWChar, 20
ADOXtable .Columns.Append "Fax", adVarWChar, 20

ADOXcatalog.Tables.Append ADOXtable

Set ADOXtable = Nothing
Set ADOXcatalog= Nothing
I want to allow each field to have zero length, how do I do this?

Programmatically Creating A Database With Tables.
How can I programmatically create a database and tables selecting the information I want to send to the table from my existing database?
Database Name: TestData.mdb

Table1: Customer
Send this info to the new table :
SELECT * FROM Customer WHERE RepCode = " & myRep, cn, .., ..

Table2: SALES
Send all sales but then I wont display the sale unless the sales customercode is found in the customer file

Creating Dsn Name Programmatically(M.S.Access Driver)

how to create data source name(dsn) programmatically in vb(Microsoft access driver)

Programmatically Export .dbf Files To A New Access Database
I have a list of .dbf files in a specific location, I need to import all these into a New Access Database. Can anyone help?

How To Add A Field Programmatically To Access Database Using ADODB?

I have been trying different things with no luck. I always get an error when trying to append the field.
The last thing I tryed was this:
  Public adoConn As New ADODB.Connection
  Public adoRS As New ADODB.Recordset

Private Sub cmdContinue_Click()
On Error GoTo ErrorTrap

        'Get data from external db
        Dim sqlQuery As String

        adoRS.CursorLocation = adUseClient
        adoConn.OpenSchema adSchemaTables
        adoRS.Open "Despatched", adoConn
        Dim i As Integer
        Dim found As Boolean
        For i = 0 To adoRS.Fields.Count - 1
            If adoRS(i).Name = "ToFollow" Then
                found = True
                Exit For
            End If
        Next i
        If Not found Then

'Next line gives an error
            adoRS.Fields.Append "ToFollow", adVarChar

            MsgBox "Database updated correctly", vbInformation, "Success!"
            MsgBox "There was no need to update your database as the required field already existed", vbInformation, "Check completed"
        End If
Exit Sub

MsgBox "Error Number: " & Err.Number & vbCrLf & Err.Description & vbCrLf & vbCrLf & "Debug Information:" & vbCrLf & _
"Project1.Form1.cmdContinue_Click" & IIf(Erl > 0, "." & Erl, ""), vbCritical, "Error Occurred"
End Sub
Public Sub DisconnectExternal()
End Sub

Public Sub ConnectExternal()
        ' Matching a single character use underscore '_' eg: 'SMI_H'
        adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & App.Path & "Mydatabase.mdb;"

End Sub

Any ideas? What can I use to add a field if it does not exist?
I get the error 3219: operation is not allowed in this context... What context does it need?

Help please!!!!

HOWTO: Programmatically Create A DSN For MS-Access Database With VB6
How can I programatically create User DSN In VB6 for MS-Access Database. pls help. its urgent.

Programmatically Installing Access Database During Setup
I packaged my application (using VB6), which includes an Access database on an installation cd. When I want to install the application on a computer, I still have to manually copy and paste the Access database from my installation cd to the destination folder on the computer. And this works fine.

Is there, however, a way to do this automatically/programmatically during the set up? Maybe I have to change something in during the packaging process (I used the Packaging & Depl. Wizard)

How To Programmatically Insert An Ole Object Into An Access Database
I am working on a VBA program in MS outlook in which I am using an ADO connection to update an Access database. It works fine for my text fields, but I am not sure how to update an Embedded Object field. Anyone know how I can do this?

For example I have a database called C:/Test.mdb with table "My_Table" containing a field called "Email_Message" (data type OLE Object). I would like to insert the current Mail Item in my ActiveExplorer window into the "Email_Message" field in the database:

Function LogEmails()
    Dim msg As MailItem
    Dim rs As New ADODB.Recordset
    Dim cn As New ADODB.Connection
    Dim strSQL As String
    Dim FilePath As String

    Set msg = ActiveExplorer.Selection.Item(1)
    FilePath = "C:Test.mdb"
    Set cn = New ADODB.Connection
    With cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "data source=" & FilePath
    End With
    Set rs = New ADODB.Recordset
    strSQL = "UPDATE My_Table SET Email_Message = " & msg & " WHERE Item_ID = 2"
    rs.Open strSQL, cn
    Set rs = Nothing

End Function



Creating An Dsn Through Vb Programmatically
Respected sirs,
I want to create a key {to create an DSN for ODBC) in the windows 98 registry using VB6.0 programmatically.

After getting tips from this site I wrote the code as follows

Private Sub Command1_Click()
SaveSetting "HKEY_CURRENT_USERSoftwareODBCODBC.INI", "kar", "BufferSize", "65535"
MsgBox "done"
End Sub

when run the above code creates the above key but not in the path as i have specified above.
It creates the key in

which is the default path

but I want the key to be in the path

Is there any way to change the default path .
If yes please send me the code.

thanking you
and waiting for a code
Binary Technologies

Creating Access Database...
hi! im a newbie in vb and i usually use data object control in my database but how can i create my own database by coding? i would like to create sort like... if it is january 2004, it would create january2004.mdb... upto (monthyearx).mdb.

in (monthyearx).mdb the tablename is studlog
its field are the ff.

maybe you were just wondering why did i segregate it?...because i would like to know how much space it will consume on my harddrive and looks organize too.

is that possible to do? i have no idea how to do this! help!!!! =(

Creating Access Database
hello i want to create a database program in vb and i want the data base to be stores in a access file. but how do i create the file access database file in the first place. i need to values like name, address, and also a place where i can put a photo for every entry. thanks

Creating A Access Database
I know how to create fields in a access table through vb6, but iwas wondering if their was a way to create a table using vb6.

please help

Creating Access Database..

I dont know where do i have to post this doubt..thats y i had placed the same in both database development and classic VB sections..Please dont misunderstand.....thnx...

well..coming to my problem...I would like to create an access database (access file) at runtime..that means whenever I Clicked on a button for example.. An access database have to be created in the specified path with a name of NOW(),Later i will export some tables into that...
Nothing but like a backup application....

Hope,I explained my problem clearly...

Thanx & regards

Creating A Database In MS Access
I'm trying to create MS Access DB in run time and getting User type not defined error on the bolded line, any one knows why?

VB Code:
Public Sub CreateDB(ByVal pDatabaseUNC As String)    Dim ws As Workspace    Dim db As Database     On Error GoTo Routine_Error     'Create a new Database with the specified name    Set ws = DBEngine.Workspaces(0)     'Default Workspace    Set db = ws.CreateDatabase(pDatabaseUNC, dbLangGeneral)     'Add a new table to the newly created database    CreateTable db, “Some Table” Routine_Error:    Set db = Nothing    Set ws = Nothing    If Err.Number <> 0 Then Err.Raise Err.Number, Err.Source, Err.DescriptionEnd Sub [b]Public Sub CreateTable(ByRef pDB As Database, ByVal pTableName As String)[/b]    Dim tbl As TableDef    Dim fld As Field     On Error GoTo Routine_Error     'Create a table with the specified name in the database provided by the input parameter    Set tbl = pDB.CreateTableDef(pTableName)     With tbl        'Add fields        Set fld = .CreateField(“Some Field”, dbText, 10)        .Fields.Append fld        Set fld = .CreateField(“Some Other Field”, dbText, 20)        .Fields.Append fld    End With     'Append table to the database    pDB.TableDefs.Append tbl Routine_Error:    Set fld = Nothing    Set tbl = Nothing    If Err.Number <> 0 Then Err.Raise Err.Number, Err.Source, Err.DescriptionEnd Sub

Is this the best way to do it or are there any better ways, any help is appreciated!

Creating An Access Database In VB
I have created a access database comprising of only three fields, however my problem is a) I am not too good at access (dunno why) and b) I have NO IDEA how to integrate an Access database into a VB program and get VB to write and read to and from it respectively.
Can anybody help me please??

Creating Access Database In VB
Hello Everyone and Good Day!

I have searched vbCity for the answer to creating a new mdb file in a directory. i have found some sample code and am getting errors. I have the references checked and all that good stuff. Here's what I have in my app:

Dim cn As ADODB.Connection
    'Must add reference "Microsoft ADO Ext. 2.x for DDL and Security" to use adox.
    Dim NewDB As New ADOX.Catalog
    Dim CNStr As String
    Dim DBPath As String
    DBPath = App.Path + "" + "Database" + "FMS2.mdb"
    CNStr = "Provider=Microsoft.Jet.OLEDB.4.0;" _
            & "Datasource = " & DBPath
    NewDB.Create (CNStr)
' End If

The error that I am getting is this one:
Run-time error '-2147467259 (80004005)':
Could not find installable ISAM

I don't know what the problem is and would prefer not to use ADOX. Can anyone help me.
Even if you can solve using ADOX, I would appreciate that, too.


Need Help With Creating New Access Database
I am trying to create a Access .MDB file via code. The code I have works. I don't know how to create the different DATA_TYPE for the columns of Access.

the adVarWChar creates a DATA_TYPE of TEXT. My question is how do I create other DATA_TYPEs. I need to create DATE, NUMBER, CURRENCY and set to AUTONUMBER.

****** code sample ******
Public Sub CreateDataBase()
  Dim objcat As ADOX.Catalog
  Dim objMyTable As New ADOX.Table
  Set objcat = New ADOX.Catalog
  objcat.Create strConnection
  objcat.ActiveConnection = strConnection
  With objMyTable
    .Name = "Loads" 'the Name of the Table Created
    .Columns.Append "DATE_AVAIL", adVarWChar, 20 '<<< needs to be Date
    .Columns.Append "ORIGIN", adVarWChar, 50
    .Columns.Append "ORIGINST", adVarWChar, 50
    .Columns.Append "DEST", adVarWChar, 20
    .Columns.Append "DESTST", adVarWChar, 20
    .Columns.Append "STOP_OFF", adVarWChar, 20
    .Columns.Append "RATE", adVarWChar, 20
    .Columns.Append "REC_NUM", adVarWChar, 20 '<<< needs to be numeric and AUTONUMBER
    .Columns.Append "CUST_NUM", adVarWChar, 20 '<<< needs to be numeric
    .Keys.Append "PrimaryKey", adKeyPrimary, "REC_NUM" 'This names PrimaryKey
    End With
  objcat.Tables.Append objMyTable

  Set objMyTable = Nothing
  Set objcat = Nothing

'Create Blank Record
Dim rs As ADODB.Recordset

DTPicker1.Value = Date
txtOrigin.Text = "0"
txtOriginSt.Text = "0"
txtDest.Text = "0"
txtDestSt.Text = "0"
chkBxStopOff.Value = 0
txtRate.Text = "0"
txtRecNum.Text = "0"
txtCustNum.Text = "0"

' Build SQL String
   Dim strSQL As String
   strSQL = "INSERT INTO [Loads] " & _
      "VALUES " & _
      " ('" & DTPicker1.Value & "' ," & _
      " '" & txtOrigin.Text & "' ," & _
      " '" & txtOriginSt.Text & "' ," & _
      " '" & txtDest.Text & "' ," & _
      " '" & txtDestSt.Text & "' ," & _
      " '" & chkBxStopOff.Value & "' ," & _
      " '" & txtRate.Text & "' ," & _
      " '" & txtRecNum.Text & "' ," & _
      " '" & txtCustNum.Text & "' )"
 ' Connect To Database and Update
   Set cn = CreateObject("ADODB.Connection")
   With cn
      .ConnectionString = App.Path & "Available.mdb"
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Execute (strSQL)
   End With
   Set cn = Nothing

End Sub
****** end code sample ******

Any ideas on how to do this? Any code would help.
Sample of code is attached.

Thanks in advance Charles

Creating A New Access Database From VB
could someone point me in the proper direction for creating a new Access database from inside VB? checked database control, tabledef & tabledefs controls, recordset control, but can't seem to find a way, any information pointing me in the right direction would be most helpful. Thanks n' advance.


Creating/Registering A DSN Programmatically
I am in the process of converting a project to use ADO instead of RDO. This project creates a DSN "on the fly" using the "rdoRegisterDataSource" method of the RDOEngine object. Is there an equivalent ADO method, or some way to do this that does not use RDO?

Programmatically Creating A .ZIP File
How can I programmatically create and append files to a compression file, specifically a .zip file?

Creating An MS Access Database From Within Excel
Hi All

Is it possible to create an MS Access database from within MS Excel VBA?

I have so far only been able to start another instance of the MS Access application. I now want to be able to :-

1. Create a blank general database
2. Create a blank table with 'n' number of fields of "variant" datatypes
3. Populate this table with data from an already populated Excel VBA array, hence the need for the fields in the table to be of variant datatype! Can this entire array be dumped in to the table in one go or does this need to be done field by field on a record by record basis?
4. Sort this table on 'm' ('m' obviously <= 'n') number of fields in the independently specified sort order for the 'm' sort fields, preferably using appropriate SQL statement to sort the table/query.
5. Repopulate the array with the sorted data. Does this also needs to be done on dataitem by dataitem or can the whole table be dumped back in to the array?

The reason I want to go this path is because of the high degree of probability of having to work with more 65536 rows of data with the need to sort the same on more that 3 fields on independently specified sort order for each of the sort fields - hence would not ordinarily be able to do it on an Excel worksheet or array sorting using quicksort sorting on 3 or 4 dimensions (columns) - the code that I got given by Carl Porch (refer to one of his older posts on this forum so far only works for me for sorting on independent sort order on upto 2 dimensions, I am currently working on this with his assistance to be able to do it on 3 & 4 dimensions).

The other reason of going this way is that I can then have a highly generic "modus operandi" for sorting a dataset on any number of fields on independently specified sort orders for each of the sort fields instead of having specific codes for sorting on 'x' number of fields.

Any assistance shall be highly valued.

Best regards

Deepak Agarwal

Creating An Array From An Access Database
Can anyone tell me what the code would look like to take the records from a field in a database and place them into an array?

Creating A Blank Access Database?
Is there a way to create a blank access database purely from code in VB?

Creating An Access 97 Database Through VB Code ?
Can anyone shed any light on how to create an Access 97 database, on the fly using VB code ??


Creating A Database Application With VB V. Doing It All In Access
Okay, here is the situation:
I want to develop an application that tracks drawings, job costing, and Job tracking. I have been asked to develop the application in Access, instead of VB 6.0. Now I realize that I can do VB codeing in Access, but isn't there a problem with Transactional tracking if I develop the application in Access? What about scalability. If I develop the app seperate from Access, it makes it easier for me to recode for say MySQL, as I am mostly using SQL statements and only in one part of my app am I using Recordsets. Also, although there are few users that will be in the database concurrently, I am concearned about netwrk traffic, as well as the ability of Access to handle those connections, where as if I develop the app through VB independent of Access, I can have Optimistic locking. Can I also do that if I develop through Access though?
Thanks for any help, and insight that you can shed on this.


Creating .exe To Open Access Database
Hi all -

I need to create a new .exe file using VB that will open my Access DB (MHC_Database.mdb) and autofill 2 fields. It needs to fill a specific "UserID" and "Password" fields and hit enter to open the correct form (access takes it from there). I'm green to VB other than minor stuff in Access but have been told I need VB for this. Any ideas would be great. Thanks.

MS Access Database Problem - Creating
i have made a list of possible relationships from my given senario but i am not sure on how to then go about putting it in to an ER diagram and what entities should be there and the attriubutes to go with them. the part i am mainly stuck on is what thing in each entity is going to be the primary key. i have thought to my self that there maybe/has to be one linking table where information from all the different tables join together. i do now that when i start the database/application i need to ask for the persons NHS number and from there i should be able to dervie whether they are an employee or patient at the hospital.

any suggestions would be gratefully accepted.

this is my given senario
The Get Well Hospital (GWH) is considering creating a database to keep track of all aspects of the hospital.

GWH wants to store the information about employees, patients, wards, beds, and treatments in the database.

Each employee has a NHS Number, name, and salary. For some types of employees, additional information should be recorded. For a doctor, the highest degree obtained and where and when the degree is obtained should be recorded. For a nurse, the level of skill (1 to 5) should be recorded.

Each patient has a NHS Number, a name, an address (street, city, county, and postcode), and several contacts (name and phone number). The patient’s disease should also be recorded.

GWH has several wards with different functions. Each ward has a number, name, main function, and number of beds.

Each ward has several beds that may contain patients. Beds are numbered within the ward. Two wards could both have a bed 123. The patient will be placed in a bed in a ward relevant to his/her disease.

Several nurses work in each ward and one of them is in charge of the ward. The nurse in charge of a ward must have a level of at least 4. There must be at least the same number of nurses as the number of beds in any ward.

A Patient is admitted to the hospital on the advice of the consultant doctor.

The patient will be treated by a number of doctors with various treatments. Each treatment will begin at a specified date and place, and will be associated with a fee. A unique treatment number is assigned to each treatment. A doctor cannot treat more than 20 patients on any single day. Every treatment has a result that is stored as medical history for the patient.


Creating A Calendar From An Access Database
Hello, I have a question I was hoping I could get some help on.
We have several small access databases where I work, and I am working on one wherer we have the plane flights for the week listed in a query. I need to take this flight listing and create a calendar that will list where flights are going each day, and when pilots have the day off.

First Question: Will VBA and access do this or do I need a full version of VB?

Second Question: Can anyone help? I've looked around for a few days now and haven't found any similar calendar scenarios. Is this possible, or would I be better of using something else? Any help would be greatly appreciated.


Creating DataGrid Programmatically Using WebControls
Hi All,

I am trying to Make a component which will make the datagrid programmatically. So I have created a class myDataGrid.vb and in that I am instantiating and creating a DataGrid.

Dim myDataGrid As New DataGrid()
Dim DeleteColumn As New ButtonColumn()
Dim EditColumn As New EditCommandColumn()

With myDataGrid
.BackColor = System.Drawing.Color.White
.HeaderStyle.Font.Size = System.Web.UI.WebControls.FontUnit.XSmall
.HeaderStyle.Font.Bold = True
.BorderStyle = BorderStyle.Solid
.ShowFooter = False
.CellPadding = "1"
End With

With DeleteColumn
.ButtonType = ButtonColumnType.LinkButton
.Text = "Delete"
End With

With EditColumn
.ButtonType = ButtonColumnType.LinkButton
.UpdateText = "Post"
.CancelText = "Cancel"
.EditText = "Edit"
End With

Now I have a Procedure which will execute delete:

Sub DeleteLine(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)
  'Delete statements
End Sub

My problem is How to add to tell the program to go and execute DeleteLine procedure when the user will click on Delete linkButton on the Grid ????

I tried
AddHandler myDataGrid.DeleteCommand, AddressOf Me.DeleteLine
this doesn't work

I have worked with datagrid and I used datagrid.ondeleteCommand property to specify the procedure to be executed. But when I use the .vb class, ondeleteCommand property is not displayed.

I want to execute the procedure DeleteLine and EditLine depending on whether the user clicks on Delete LinkBUtton or Edit LinkButton.

Please reply,
Thanks a million,
Rishi Chaturvedi

Programmatically Creating Word Docs + RTF?
    i wanted to create some reports for my client and dynamcally create some sort of docs in RTF's and Ms Word Docs so he can take the reports any where and view them or even print them ..

    I know that using automation you can create an instance of Word and input text etc ... the problem is that the clients wont neccesarily have MS word so i need to use some sort of libary ?? any ideas ??

    The same goes for RTF? -- i thought of using the Rick Text Box control but i'm having trouble saving the pics --> it desnt support JPEG and when i insert BMP;'s the file size rockets!


best regards,
                    sniGGer -- the one & only
                    V. Sandeep Koniki
                    Me want MCSD!!!
There is ALWAYS a way out ... its usually staring you in the face!

Need A Little Help Creating My Update Command Programmatically
Can anyone please help with creating this Update command ? When it comes to Parameters I always seem to have a problem.

I have a Header Table an a Detail Table, I need to Update Column 3 in each detail record with the contents of Column 3 in its respective Header record.

Here is my routine so far,

Private Sub Update_Header_Details()

Dim Det_Count As Integer = 0
Dim Head_Count As Integer = 0
Dim i As Integer = 0
Dim j As Integer = 0
Dim Head_DS As DataSet = New DataSet("tbl_Accpac_InvHeader")Dim Head_DA As SqlDataAdapter = New SqlDataAdapter("Select * from tbl_Accpac_InvHeader",Del_Conn)

Head_Count = Head_DA.Fill(Head_DS, "tbl_Accpac_InvHeader")

Dim Detail_UDComm As SqlCommand = New SqlCommand()
Dim Det_Conn As SqlConnection = New SqlConnection

Det_Conn.ConnectionString = My.Settings.VIH_Conn
Detail_UDComm.Connection = Det_Conn

     For i = 0 To Head_Count - 1

           Dim Detail_DS As DataSet = New DataSet("tbl_Accpac_InvDetails")
           Dim selCMD As SqlCommand = New SqlCommand

           selCMD.CommandText = "Select * from tbl_Accpac_InvDetails Where Field8 = " + "'" + Head_DS.Tables(0).Rows(i).Item(4) + "'"

           Dim Detail_DA As SqlDataAdapter = New SqlDataAdapter(selCMD.CommandText, Del_Conn)
           Dim Det_UDCMD As SqlCommand = New SqlCommand()
        Det_UDCMD.CommandType = CommandType.Text
           Detail_UDComm.CommandText = "Update tbl_Accpac_InvDetails SET Field2 WHERE Field2 = @Field2 "
           Detail_UDComm.Parameters.Add("@Field2", SqlDbType.Char, 255, "Field2").Value = Head_DS.Tables(0).Rows(i).Item(2)
           Detail_DA.UpdateCommand = Detail_UDComm

           Det_Count = Detail_DA.Fill(Detail_DS, "tbl_Accpac_InvDetails")

           For j = 0 To Det_Count - 1
                   Detail_DS.Tables(0).Rows(j).Item(2) = Head_DS.Tables(0).Rows(i).Item(2)

           Detail_DA.Update(Detail_DS, "tbl_Accpac_InvDetails")


End Sub

Thanks in advance


Creating A Pie Chart From Data In An Access Database
I have my access database connected to the chart, however I would like my chart to be a pie chart that takes the sales amount for each person and makes them a slice of the pie. Not sure how to set the pie chart to make it work. I currently get a single pie for each person I have. Can anyone help?

This is my database.

name Amount
Sam 3000
Bill 4000
dave 2500
ken 5000
chris 6000

Creating An Access Database, Booleans, Checkboxes

I am using the code below in part to create an Access database. When viewing this database in MSAccess, the boolean field shows either a -1 or 0 for Yes/No (true/false). I know in Access you can set the Display Control under the Lookup tab to either a textbox or checkbox. I need to set this somehow in code to checkbox when I create the database. Does anyone know how?


VB Code:
With tbl    Set .ParentCatalog = Cat        .Name = "READINGS"                .Columns.Append "INTOL", adBoolean         .Columns.Append "MMSECDISP", adVarWChar, 30End WithCat.Tables.Append tbl

Creating Access Database, And Tables At Runtime
am making an app which uses a access database, but if the current database becomes corrupt or unreadable, because the user messing with the format of the database...

How would i make a Database in VB, with tables and relationships.

Or is that going to be difficult?

Any help appreciated


Post No. 300

Creating An Access Database Durin Runtime
I want to create an access database during Runtime...and then I wanna create 4 tables in that Database and then those tables will have 5 columns's this possible?? Please help

Adding Records/creating New Access Database In VB
By using code, how'd I add a record to a field?

Creating System Dsn For Access Database Urgent
hai i want to create a system dsn for my access database
how to do that
kindly help me

Creating A CSV File From Access Database Table Using VB6
i have an access file which contain 5 tables , each of these 5 tables contain a number of columns
what am trying to do is , to store the tables of these access files as CSV file using VB6 ( Visual Basic 6)

but am stuck since i didnt get used to use VB6 previously

any suggestions

Creating Workbooks(with Macros And Userforms) Programmatically
I ve a template(.xlt) file.The file contains some macros and a user form.When I open the xlt file i get the user form..

In some other workbook I ve written code to create new workbooks using the above mentioned template file as the template...

Though my code creates the new workbook but that workbook does not have the user form...

I want the userform to be available in each workbook I create...

Any one help please

Creating An Access Database From Visual Basic Code
hi ,

can anyone help me in creating an access 200 database from the visual basic code.? i got some code from the net which creates database from the VB but when i use the code it is giving me error. iam posting the code in this thread. please go through and give me reply. this is part of my thesis work so please help me out.
thank you.

NOTE: i have visual basic 6.0 and access 2000 on my computer

code that creates database:

Option Explicit

Sub CreateAccessDatabase()
Dim CatDB As ADOX.Catalog
Set CatDB = New ADOX.Catalog
CatDB.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=myMDB.mdb"
Set CatDB = Nothing
End Sub

Sub CreateTable()
Dim CatDB As ADOX.Catalog
Dim TabDB As ADOX.Table

Set CatDB = New ADOX.Catalog
Set TabDB = New ADOX.Table
'open the database
CatDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=myMDB.mdb"
'create new table object
With TabDB
.Name = "MyTable" 'set name
'add fields and specify datatype
.Columns.Append "First Name", adVarWChar
.Columns.Append "Last Name", adVarWChar
.Columns.Append "Age", adInteger
End With
'add the table to database
CatDB.Tables.Append TabDB
Set CatDB = Nothing
Set TabDB = Nothing
MsgBox "Database and Table created..."
End Sub

Private Sub cmdCreate_Click()
End Sub

Compile time Error:

User defined type not defined
and the control goes to this line

Dim CatDB As ADOX.Catalog

Please give me reply on this

Creating, Accessing, And Adding A Password To Access Database?
I haven't touched VB since version 4 and 5 and lost all my old work with a hard drive crash.

I am wondering if anyone has a tutorial on creating a database, accessing it, and making sure it has a password.

I want to create a small application and it should create a database on the fly, make sure it is password protected, and I need to know how to retrieve records from it.

Creating A Flat File From Access Database Through A VB Script
I'm trying to create a Flat file(not a CSV) out of an Access database through a vb script.
Could any one suggest me how do I do that?
I know how to connect and disconnect to the database using ADO's.
The script has to create a flatfile at a predetermined place over the network and fill it out with the data.

Thanks in advance.

Deleting / Creating Relationships Within An Access Database Through ADOX Coding
I have the following in a module named modBas:

Public Function CreateRelationship(strTblP As String, strTblF As String, strIDP As String, strIDF As String, strRelName As String, BolUpdate As Boolean, BolDelete As Boolean) As Boolean

Dim cat As ADOX.Catalog

Dim fk As ADOX.Key
Dim tbl As ADOX.Table
Dim keys As ADOX.keys
On Error GoTo HandleErrors
'Get the catalog for the current database
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
'Get the keys collection for the many-side table
Set tbl = cat.Tables("tblOrders")
Set keys = tbl.keys
'Create the foreign key
Set fk = New ADOX.Key
fk.Name = "tblOrders"
fk.Type = adKeyForeign
fk.RelatedTable = "PROJ_ME"

Set fk = Nothing
Set keys = Nothing
Set tbl = Nothing
Set cat = Nothing
Exit Function

Select Case Err.Number
Case adhcErrObjectExists
' If the relationship exists,
' just delete it and try again

Case adhcErrKeyObjectExists
tbl.keys.Delete fk.Type = adKeyPrimary

Case Else
MsgBox "Error: " & Err.Description & _
" (" & Err.Number & ")"
CreateRelationship = False
Resume ExitHere
End Select
End Function

Then in my startup form named frmStartup I have a command button that will allow a table to be imported. I know that I first have to delete the relationship between the two tables thus I have the command line CreateRelationship = False:

Private Sub cmdImportTable_Click()
'On Error GoTo Err_cmdImportTable:
If MsgBox("Are you sure you want to import and erase over the current PROJ_ME table?", vbYesNo + vbQuestion) = vbYes Then

CreateRelationship = False
DoCmd.DeleteObject acTable, "PROJ_ME"
DoCmd.TransferDatabase acImport, "Microsoft Access", "C:ZZ OPERATIONS PROGRAMSImportTable.mdb", acTable, "PROJ_ME", "PROJ_ME"
CreateRelationship = True
MsgBox "Finished importing data..."
End If
Exit Sub
MsgBox "Something's wrong - you are unable to import the table. Please make sure you've extracted the ImportTable.mdb file to C:ZZ OPERATIONS PROGRAMS If you've done that correctly then the PROJ_ME table is probably missing from there, you may have to get the file sent again!"

End Sub

Now when I click on the cmdImportTable button , I get an error message - Compile error: Function call on left-hand side of assignment must return Variant or Object. The error highlights CreateRelationship = . I am totally lost as to what I need to do to make this work. I know Public Functions are pretty important if I'm going to use VBA code but I don't understand them fully from the training books that I have. Please someone explain about how I should go about doing this the right way. I'm kinda of a newbie on this... Thank you for your help.

Creating A ADODB Database Function Module In Access 2003 Using Separate ...
Hi everyone,

Program: MS Access 2003

I have written the following module in an MS Access database for database operations, ie getting recordsets and editing records. When the database is loaded the method SetConnData is executed to set the connection data for the connection. This is a new function I've created as now I have disconnected the tables from this database, previously I just used currentproject.connection as the connection in the other methods.

The function QueryDatabase returns a recordset containing the data specified in an SQL string supplied when called. This function is used for SELECT type SQL statements. The Update database function accepts an array of SQL strings executed in order as a single transaction. If any of the updates fail the transaction is rolled back. This is used for INSERT INTO, UPDATE and DELETE SQL statements where there is no returned data. Returns a boolean value depending on the outcome of the transaction, true if transaction performed and false if fails.

This worked well before I separated the tables from the current database (the tables are now hardcoded as strDataSource = "C:XXXXXXXXData.mdb" in the SetConnData sub) and moved them to a separate file. Now I'm having problems running an SQL statement that used to work before. The statement seems to be alright because when run from database containing the tables it returns records but when I run it from the current DB it contains no records!

I've tried testing the Querydatabase function in the Immediate window using the same SQL statement and it doesn't return records that way either. I tried using a simple SELECT * statement and counting the records which seemed to work.

Can anyone see where I've coded incorrectly? I am wondering if I need to look at the record locking and cursortype setting etc...

Option Compare Database
Option Explicit

Global connRM As ADODB.Connection

Public Sub SetConnData()
    'Sets the values for the Connection connRM
    Dim strDataSource As String
    strDataSource = "C:XXXXXXXXData.mdb"
    Dim strUserId As String
    strUserId = "Admin"
    Set connRM = New ADODB.Connection
    With connRM
        .ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" _
        & "User ID=" & strUserId & ";" _
        & "Data Source=" & strDataSource & ";" _
        & "Jet OLEDBatabase Password='';" _
        & "Jet OLEDB:Engine Type=5;" _
        & "Jet OLEDBatabase Locking Mode=1;"
    End With
End Sub

Public Function QueryDatabase(ByVal strSQL As String) As ADODB.Recordset
    'Function is supplied an SQL statement from the calling code and
    'returns a recordset with the returned results.
    'Checks if the connection is open and if not then opens it.
    If connRM.State = adStateClosed Then
    End If
    Dim rsNew As New ADODB.Recordset
    'Fills the recordset with the required data.
    With rsNew
        .CursorLocation = adUseClient
        .Open strSQL, connRM, adOpenForwardOnly, adLockReadOnly
    End With
    'Returns the Recordset
    Set QueryDatabase = rsNew
    'Sets the recordset equal to nothing, clean up statement
    Set rsNew = Nothing

End Function

Edited by - Pico on 10/19/2006 9:29:38 PM

Creating Program To Develop Text And Check Boxes Based On Access Database
My next project that I want to tackle is a program that takes an Access database and creates text boxes and check boxes based on information in a table. It would read any boolean Data Types as a check box and any Text, Date, or Number types as a text box.

I'm pretty sure I've got the exact coding to do a lot of the individual parts to carry out this but my problem is having trouble more with the total design of it. I want to know what's the best way to arrange my data in an access table so that it could pull the information properly.

You it searches for each field if a text box or check box...
then it would place it on a form line by line in sectioned off by groups. It would then of course adjust the form's width based on the width of the labels and text boxes (which would probably be based on field size in the access database) But how can I accomplish this in one table. Do I have to use multiple tables?

The reason why I want to have more control over creating text boxes and check boxes on my entry because I am constantly asked to add a new one here and there. All I want to do is to add the specifications in an access database and it will create it for me without having to re-design the form all the time.

Do you know where I could find some good info on someone who has tackled the above project already? It doesn't have to be an access database though because I use the ADO connection to it. Thanks for any insight and pointers.


How To Set Database Perimission Programmatically
I am updating an installation program that performs conversion of databases to Access 2000. One of the tasks I need to perform in the installation program is to set the Database permission for the Users group to None. I am familiar with programmatically setting permissions for tables, forms etc, but am not sure how I access the Permissions property for the Database object.

eg. To set security for a Form, you retrieve the Form document by going:

set db = CurrentDB()
set doc = db.containers("Forms").Documents("MyFormName")
doc.UserName = "Users"
doc.Permissions = dbSecWriteSec
what is the equivalent code for setting permission to the Database itself?

Change Database Location Programmatically
Hi guys,

I have a crystalreport running perfectly thru crystal designer. I am accessing the crystal report via VB6 and displaying it on screen perfectly. Now this works on the Dev systems. If the .rpt file is copied across to the TEST systems( which points to another database), I get a message "Logon Failed". If I manually change the Database Location in each report to point it to the new database, it work fine.

My question is: Is there a way to change the database location programmtically thru vb6.

I use this code in my app to logon to the tables.

For i = 1 To cReport.Database.Tables.Count 'Oracle Server Logon Info
  cReport.Database.Tables(i).SetLogOnInfo "", "", CStr(userid), CStr(pwd)
Next i

This logs on to the database location updated in the .rpt file. I want to change this database location programmtically. Is there any way to do it.

I use Crystal 9 with Vb6.


VB &gt; Access2000 Database Upgrade Programmatically
Hope there are some good gurus out there that can lend a hand to this discussion. I'm 0 for 5 so far on various forum but I can't believe this is either so strange or difficult.

Can anyone here give me some advice on methods and processes for automating the upgrade of an existing Access DBs as my VB6 (not NET) application develops and/or grows? I'm using ADO.

The story goes like:

MyApp.exe and my MyAccess.MDB are working fine together.

Client asks for modifications that result in program modifications as well as adding a Table or Field to the DB or changing the datatype of a field.

I make the necessary changes in my code and my local MDB copy but need to upgrade both her current EXE & DB to work with the changes. Whereas the EXE is no problem, what is the recommended method to update the client's DB?

Am I wrong in the assumption that I need to write several routines that include:

Thoroughly detecting the current DB structure vs new DB to determine the upgrade path (ie. what tables, fields need amending).
Adding necessary Tables.
Adding necessary Fields to existing Tables and possibly populating certain fields.
Changing datetypes and reformatting the current data in these fields.

I know this might be a complicated issue but appreciate any & all your wisdom. I'm sure others are interested in this topic as their programs and DBs advance.

Xie xie (thanks) to all in advance

the maj

Edited by - the maj on 11/10/2007 12:23:59 AM

Copyright © 2005-08, All rights reserved