creating tables for data like in access how to get started

[kirkdm22]

how do set up tables like in access in visual basic. where do start? nothing to complicated just enough to start me off.

[Lonely Wolf]

I use access to create tables, if you want i can send you some example code on how to create an access table via VB.
Let me know

[kirkdm22]

yeah could you please send me some samples please of access migration(tables etc.) and also if you could send a sample of creating tables within vb in itself. cheers kirk

[Lonely Wolf]

here the code to create an .mdb(named "prova") file with a table (named "table") composed of 3 fields ("f1" text,"f2" long integer, "f3" true/false).


'''''''''''''''''''''''''''''''''''''''
'REQUIRES: Reference to MS DAO in VB project
'NOTE NOTE NOTE: Code does *not* check Validity of Destination Path!!
'''''''''''''''''''''''''''''''''''''''

public Function CreatedNewDB(byval sDestDBPath as string, byval sDestDBPassword as string) as Boolean
CreatedNewDB = false

on error GoTo Err_Handler

If sDestDBPassword <> "" then
set NewDB = Workspaces(0).CreateDatabase(sDestDBPath, dbLangGeneral & ";pwd=" & sDestDBPassword)
else
set NewDB = Workspaces(0).CreateDatabase(sDestDBPath, dbLangGeneral)
End If

'Now call the functions for each table

Dim b as Boolean

b = CreatedNewTBLtable
If b = false then
CreatedNewDB = false
NewDB.Close
set NewDB = nothing
Exit Function
End If

CreatedNewDB = true

Exit Function

Err_Handler:
If Err.Number <> 0 then
'Alert & Close Objects, could be altered to Raise the error
Msgbox "error Creating Copy Database." & vbCr & Err.Number & vbCr & Err.Description
CreatedNewDB = false
NewDB.Close

set NewDB = nothing

Exit Function
End If
End Function

private Function CreatedNewTBLtable() as Boolean


Dim TempTDef as TableDef
Dim TempField as Field
Dim TempIdx as Index

CreatedNewTBLtable = false

on error GoTo Err_Handler

set TempTDef = NewDB.CreateTableDef("table")
set TempField = TempTDef.CreateField("f1",10)
TempField.Attributes = 2
TempField.Required = false
TempField.OrdinalPosition = 1
TempField.Size = 255
TempField.AllowZeroLength = false
TempTDef.Fields.Append TempField
TempTDef.Fields.Refresh

set TempField = TempTDef.CreateField("f2",4)
TempField.Attributes = 1
TempField.Required = false
TempField.OrdinalPosition = 2
TempField.DefaultValue = 0
TempTDef.Fields.Append TempField
TempTDef.Fields.Refresh

set TempField = TempTDef.CreateField("f3",1)
TempField.Attributes = 1
TempField.Required = false
TempField.OrdinalPosition = 3
TempTDef.Fields.Append TempField
TempTDef.Fields.Refresh

NewDB.TableDefs.Append TempTDef
NewDB.TableDefs.Refresh

'Done, Close the objects
set TempTDef = nothing
set TempField = nothing
set TempIdx = nothing

CreatedNewTBLtable = true

Exit Function

Err_Handler:
If Err.Number <> 0 then
'Alert & Close Objects, could be altered to Raise the error
Msgbox "error Creating Database Table: table" & vbCr & Err.Number & vbCr & Err.Description
set TempTDef = nothing
set TempField = nothing
set TempIdx = nothing

CreatedNewTBLtable = false
Exit Function
End If
End Function