PictureMgr: Create Table in backend

[Ron Dorn via AccessMonster.com]

I am using Arvin Meyer's PictureMgr for my database, which is split. I have
the forms all working, but I want to build the new table created by the code
on the back-end not in the front end. I am having a hard time understanding
where to post and what the code would be to do this. Can someone point me in
the right direction - my knowledge of VBA is limited but growing daily!

 

[pietlinden]

I am using Arvin Meyer's PictureMgr for my database, which is split.  Ihave
the forms all working, but I want to build the new table created by the code
on the back-end not in the front end.  I am having a hard time understanding
where to post and what the code would be to do this.  Can someone pointme in
the right direction - my knowledge of VBA is limited but growing daily!

If strDatabaseName = "[Current]" Then
Set db = CurrentDb
Else
If Dir(strDatabaseName) = "" Then
Set db = DBEngine.CreateDatabase(strDatabaseName,
dbLangGeneral)
Else
Set db = DBEngine.OpenDatabase(strDatabaseName)
End If
End If


You have to set the db variable to point at your backend. So...

Set db = DBEngine.OpenDatabase("C:\DirectoryForBE_Database
\BackEnd.MDB")

and everything else you just leave alone.

 

[rpdorn via AccessMonster.com]

I am using Arvin Meyer's PictureMgr for my database, which is split.  I have
the forms all working, but I want to build the new table created by the code

[quoted text clipped - 4 lines]
If strDatabaseName = "[Current]" Then
Set db = CurrentDb
Else
If Dir(strDatabaseName) = "" Then
Set db = DBEngine.CreateDatabase(strDatabaseName,
dbLangGeneral)
Else
Set db = DBEngine.OpenDatabase(strDatabaseName)
End If
End If

You have to set the db variable to point at your backend. So...

Set db = DBEngine.OpenDatabase("C:\DirectoryForBE_Database
\BackEnd.MDB")

and everything else you just leave alone.

I'm still stuck. My backends are not in a fixed location as the user can
place them locally or on the network. I have been using mydbpath=Dir(Mid
(currentdb.TableDefs("tblName").[Connect],11)) to get the location and file
name of the backend for other code in the db. I have tried placing this in
the location of the backend where I think you indicate:

Else
Set db = DBEngine.OpenDatabase(Dir(Mid(CurrentDb.TableDefs("tblName").
[Connect], 11)))
End If

and it still creates the new table in the front end. I also tried a couple
of variations, and I am not finding any luck.

 

[rpdorn via AccessMonster.com]

On Jan 22, 9:05 pm, "Ron Dorn via AccessMonster.com" <u57259@uwe>
wrote:

[quoted text clipped - 21 lines]

and everything else you just leave alone.

I'm still stuck. My backends are not in a fixed location as the user can
place them locally or on the network. I have been using mydbpath=Dir(Mid
(currentdb.TableDefs("tblName").[Connect],11)) to get the location and file
name of the backend for other code in the db. I have tried placing this in
the location of the backend where I think you indicate:

Else
Set db = DBEngine.OpenDatabase(Dir(Mid(CurrentDb.TableDefs("tblName").
[Connect], 11)))
End If

and it still creates the new table in the front end. I also tried a couple
of variations, and I am not finding any luck.

The suggestion did not work for me, as there are many other issues to deal
with. For anyone else looking for such a procedure I finally figured it out
after much research:

'----------------------------------------
Sub ReadFileInfos(strDatabaseName As String, strTblName As String, _
strFolderName As String)

Dim strDataFile As String
Dim db As DAO.Database
Dim rs As Recordset
Dim tdf As DAO.TableDef
Dim fld As Field
Dim idx As DAO.Index, fldIndex As DAO.Field
Dim fFormat As Property
Dim Cancel As Boolean

On Error GoTo Err_Handler

DoCmd.Hourglass True

giMainFolderStrLen = Len(strFolderName)


strDataFile = CurrentDB.TableDefs("tblName").Connect
strDataFile = Mid$(strDataFile, InStr(1, strDataFile, "=") + 1)
Set db = DBEngine.OpenDatabase(strDataFile, True)

'Now create the table in the backend
Set tdf = db.CreateTableDef(strTblName)

Set fld = tdf.CreateField("IDNum", dbLong)
fld.Attributes = fld.Attributes + dbAutoIncrField
tdf.Fields.Append fld

Set fld = tdf.CreateField("FilePath", dbText, 255)
tdf.Fields.Append fld

Set fld = tdf.CreateField("FileName", dbText, 255)
tdf.Fields.Append fld

Set fld = tdf.CreateField("Version", dbText, 50)
tdf.Fields.Append fld

Set fld = tdf.CreateField("FileDate", dbDate)
tdf.Fields.Append fld

Set fld = tdf.CreateField("FileLength", dbDouble)
tdf.Fields.Append fld

Set fld = tdf.CreateField("Description", dbText, 255)
tdf.Fields.Append fld

Set fld = Nothing
db.TableDefs.Append tdf


Set idx = tdf.CreateIndex("PrimaryKey")
idx.Primary = True
idx.Fields.Append idx.CreateField("IDNum")
tdf.Indexes.Append idx

Set idx = Nothing
Set tdf = Nothing

'And now create the link in the front end
Set tdf = CurrentDB.CreateTableDef(strTblName)
tdf.Connect = ";DATABASE=" & strDataFile
tdf.SourceTableName = strTblName
CurrentDB.TableDefs.Append tdf


Set rs = db.OpenRecordset(strTblName)

ReadFolderInfo rs, strFolderName & "\"
rs.Close

If strDatabaseName <> "[Current]" Then
db.Close
Else
Set db = Nothing
End If

DoCmd.Hourglass False

Err_Handler:
If Err = 3010 Then
MsgBox ("The new table name you entered already exists. Please change it
and try again."), vbExclamation
Cancel = True

End If

End Sub
'------------------------------------------

The only issue I still have is that other users cannot see the new table in
their copies of the front end, and I am looking for code to check and reload
all tables in a back-end that they may open.