[RESOLVED] Creating db Table during runtime

I have this code and it will works like this

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

Code:

---

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

Code:

---

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

Code:

---

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

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?

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

vb Code:

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

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

vb Code:

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

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

vb Code:

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

Quote:

---

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?

vb Code:

Msgbox app.path

Hope this helps...

lol now where have I seen that FileExists code before ;)

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

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

:D

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

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

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

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

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

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

Code:

---

    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

---

Try this function...

vb Code:

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

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

Thank you for your help KoolSid

I finally got it working.

Thanks again
jlt7