[RESOLVED] Creating db Table during runtime

[jlt7]

I have this code and it will works like this

the Set db1 = OpenDatabase("C:\temp.mdb")

On Error GoTo errorhandler

Set db1 = OpenDatabase("C:\temp.mdb")
Set td = db1.TableDefs("SubBrand")

On Error Resume Next
Set f1 = td.Fields(strString)
    
    If Err.Number = 0 Then
        MsgBox "Exists"
    Else
        Set td = db1.TableDefs("SubBrand")
        
        Set fl = td.CreateField(strString, dbText)
        td.Fields.Append fl
    End If

Exit Sub

errorhandler:

If Not db1 Is Nothing Then db1.Close
Err.Raise Err.Number
Exit Sub

But not like this

On Error GoTo errorhandler

Set db1 = OpenDatabase("F:\VB\Baseball\database.mdb")
Set td = db1.TableDefs("SubBrand")

On Error Resume Next
Set f1 = td.Fields(strString)
    
    If Err.Number = 0 Then
        MsgBox "Exists"
    Else
        Set td = db1.TableDefs("SubBrand")
        
        Set fl = td.CreateField(strString, dbText)
        td.Fields.Append fl
    End If

Exit Sub

errorhandler:

If Not db1 Is Nothing Then db1.Close
Err.Raise Err.Number
Exit Sub

or like this

On Error GoTo errorhandler

Set db1 = OpenDatabase(app.path & "\database.mdb")
Set td = db1.TableDefs("SubBrand")

On Error Resume Next
Set f1 = td.Fields(strString)
    
    If Err.Number = 0 Then
        MsgBox "Exists"
    Else
        Set td = db1.TableDefs("SubBrand")
        
        Set fl = td.CreateField(strString, dbText)
        td.Fields.Append fl
    End If

Exit Sub

errorhandler:

If Not db1 Is Nothing Then db1.Close
Err.Raise Err.Number
Exit Sub

can somebody tell me why this won't work with my database and does with the temp database that I created when testing my code.

by the way F: is an external HD if anyone is wondering

[RobDog888]

So you are doing this in Access VBA or VB6 with an Access reference?

What OS are you using? Do you have permissions to the folders? If Vista then the file locations may be Virtualized. what is the error message you are getting?

[Siddharth Rout]

Are you trying to open a database or create one. If it is creating as the title suggests then check the link in my signature...

Regarding this

Set db1 = OpenDatabase("F:\VB\Baseball\database.mdb")

Include this one line and tell me what do you get?

Msgbox DoesItExist("F:\VB\Baseball\database.mdb")

But before you do that paste the below code in a module...

Option Explicit
 
Private Declare Function OpenFile Lib "kernel32" (ByVal lpFileName As String, _
lpReOpenBuff As OFSTRUCT, ByVal wStyle As Long) As Long
 
Private Type OFSTRUCT
    cBytes As Byte
    fFixedDisk As Byte
    nErrCode As Integer
    Reserved1 As Integer
    Reserved2 As Integer
    szPathName(OFS_MAXPATHNAME) As Byte
End Type
 
Private Const OF_EXIST         As Long = &H4000
Private Const OFS_MAXPATHNAME  As Long = 128
Private Const HFILE_ERROR      As Long = -1
      
Public Function DoesItExist(ByVal strFileName As String) As Boolean
    Dim lngRet As Long, Ofs As OFSTRUCT
    
    lngRet = OpenFile(strFileName, Ofs, OF_EXIST)
    If lngRet <> HFILE_ERROR Then
        DoesItExist = True
    Else
        DoesItExist = False
    End If
End Function

And Regarding this

Set db1 = OpenDatabase(app.path & "\database.mdb")

You can either try what I mentioned above or include this one line before the above and tell me what do you get?

Msgbox app.path

Hope this helps...

[RobDog888]

lol now where have I seen that FileExists code before

[Siddharth Rout]

Probably here?

http://www.vbforums.com/showthread.php?t=562355

This is the code that I use frequently and I have saved it in my Code Generator (In my link)... I did search the forums and got 379 links. Which link are you referring to?

http://www.vbforums.com/search.php?searchid=2234338

[RobDog888]

May 22nd, 2006 http://vbforums.com/showpost.php?p=2479326&postcount=2



But lets not clog up the thread with this. We still need to know some answers to our questions on the original issue.

[jlt7]

RobDog888:
I believe VB6 w/ access reference, I've done some db programming but still don't know all the terms.
OS is XP
and don't know about permission to folders. It is the same database in c:\temp.mdb as in F:\VB\Baseball\database.mdb - just moved it and renamed.
if you mean permission to f: folders then yes I do.

Koolsid:
Tried your code, I get an error here
szPathName(OFS_MAXPATHNAME) As Byte
compile error
constant expression required

[jlt7]

Also, I want to create a new field in an existing table sorry about that.

[GaryMazzone]

If the database has a connection in the application already then you use the alter table SQL command and execute it on the connection to the database using a command object

Alter table tablename add column columnname datatype

[Siddharth Rout]

Ok try this in the code that I gave above...

Move line number 15,16,17 in the code above to line number 5 i.e before

Private Type OFSTRUCT

and now try it

[jlt7]

Ok Koolsid, I looked at your Creating Access Database via Code and I finally got it to add a field to the database, but how do I have it check if the field already exists in the table.

Heres the code I have

    Dim dbDatabase As Database
    Dim td As TableDef
    Dim f1 As Field

    db.Close
    Set dbDatabase = Workspaces(0).OpenDatabase(App.Path & "\database.mdb")
    
    Set td = dbDatabase.TableDefs("SubBrand")
    
    Set f1 = td.CreateField("txtField1", dbText)
    td.Fields.Append f1
    dbDatabase.Close
    Set db = OpenDatabase(DBPath)

    
    MsgBox "New Field Created - '" & sNewDBPathAndName & "'", vbInformation

[Siddharth Rout]

Try this function...

Function FieldExists(ByVal fieldName As String, ByVal tableName As String) As Boolean
    Dim dbDatabase As Database, td As TableDef, f1 As Field
    
    Set dbDatabase = CurrentDb
    Set td = dbDatabase.TableDefs(tableName)
    
    For Each f1 In td.Fields
        If f1.Name = fieldName Then
            FieldExists = True
            Exit For
        End If
    Next
End Function

[si_the_geek]

Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)

[jlt7]

Thank you for your help KoolSid

I finally got it working.

Thanks again
jlt7