Can DoCmd.TransferDatabase be used to move table to BE

[Hugh self taught]

Hi O' Wonderous gurus,

Can DoCmd.TransferDatabase move a table from the Front End to the Back End?

I've tried the following syntax without success. A msgbox shows the
strBackendPath to be correct.

DoCmd.TransferDatabase acExport, "Microsoft Access", strBackendPath,
acTable, "tblNewTemplate", strNewTbl

I'm experimenting with a few things & if I make a small form that I can
quickly import into any database I'm developing which has the ability to
transfer tables from the front end to the back end & also delete tables then
it will be quicker than navigating to the BE, opening & editting it for those
tasks. I can create them in the FE where I'm busy.

 

[David C. Holley]

To be honest if the only reason that you want to try this is for your
convenince, as opposed to an overridding business need, I wouldn't. While it
may be entirely possible, I wouldn't go through the hassle of developing
code and hammering out problems when you could just as easily open the back
end from the 'recent files' menu or from a short-cut on your desktop.

 

[David C. Holley]

And not to mention that you should be able to click and drag a table between
two open instances of Access.

 

[Dirk Goldgar]

Hi O' Wonderous gurus,

Can DoCmd.TransferDatabase move a table from the Front End to the Back
End?

I've tried the following syntax without success. A msgbox shows the
strBackendPath to be correct.

DoCmd.TransferDatabase acExport, "Microsoft Access", strBackendPath,
acTable, "tblNewTemplate", strNewTbl

What particular variety of "non-success" are we dealing with? Do you get an
error message? If so, what is it? Or do you get some unexpected outcome?
If so, what is it?

 

[Hugh self taught]

Hi Dirk,

The strange part is that I don't get any errors or beeps or anything. The
code executes but no table appears in the BE db file. I've not hidden warning
messages either. I had put in msgboxes to show the values of the strings
strBackendPath & strNewTbl directly before the transfer code to ensure I
hadn't messed something up there.

Another alternative I was trying with was INSERT INTO ...IN as below but
there I get a "Too few parameters. Expected 1." error message. That code is
listed below.

strSQL = "INSERT INTO CS_TRY (CplID, CplNumber, MaleID, FemaleID)" & _
" IN 'C:\Test_Database_be.mdb'" & _
" SELECT CS.ID, CS.CplNumber, CS.MaleID, CS.FemaleID" & _
" From CS"

The DoCmd.CopyObject just creates a link to the existing table locally so I
discarded using that route as an option .

Ultimately what I do is when I get an intricate or uncommon piece of code
working in a db then I keep it in a text file with reference to which db I
used it in so I can refer to how I used it at a later date. Most of my Access
development is for a dance association which is non-profit & the lady who
maintains the db is far from where I am. So when they ask for something to be
done I would be able to email the updated FE with a "Patch" mdb to execute
the necessary changes in the BE. So hopefully you can help me get this to
work one way or the other. I've read many threads on the forum & they argue
about using DAO or ADO etc but none actually give me a solution I can code &
use that I've seen.

Your help will be much appreciated

 

[Dirk Goldgar]

Hi Dirk,

The strange part is that I don't get any errors or beeps or anything. The
code executes but no table appears in the BE db file. I've not hidden
warning
messages either. I had put in msgboxes to show the values of the strings
strBackendPath & strNewTbl directly before the transfer code to ensure I
hadn't messed something up there.

Are you sure it didn't work? The syntax is correct, and it works fine for
me in a test I just made (using Access 2003). I've done this sort of thing
before many times with no problems.

When you ran the code, did you have the back-end (target) database open in
Access, or closed? If you had it open, did you have it open exclusively? I
would expect error 3045 to be generated in that case. If you had it open
and were looking at the Tables tab of the database window, I wouldn't expect
you to see the new table until you switched away from that tab and back
again, or else closed and reopened the database.

Another alternative I was trying with was INSERT INTO ...IN as below but
there I get a "Too few parameters. Expected 1." error message. That code
is
listed below.

strSQL = "INSERT INTO CS_TRY (CplID, CplNumber, MaleID, FemaleID)" & _
" IN 'C:\Test_Database_be.mdb'" & _
" SELECT CS.ID, CS.CplNumber, CS.MaleID, CS.FemaleID" & _
" From CS"

If you're creating a new table, you'd have to use a make-table query, not an
append query.

Ultimately what I do is when I get an intricate or uncommon piece of code
working in a db then I keep it in a text file with reference to which db I
used it in so I can refer to how I used it at a later date. Most of my
Access
development is for a dance association which is non-profit & the lady who
maintains the db is far from where I am. So when they ask for something to
be
done I would be able to email the updated FE with a "Patch" mdb to execute
the necessary changes in the BE.

I have used just this approach upon occasion, and it can work, though there
can be complications that need careful handling.

 

[Hugh self taught]

Hi Dirk,

Well I played around a bit & found my opening of the BE database was at
fault on the DoCmd.Transfer method.

The SQL method was needing to be a make table as well as open the database
then I got that to work as well. So on those I'm feeling happy.

I was trying the code I've pasted here below to create a new table but I'm
missing something because the Debug.Print only shows the BE path in the
immediate window & the table is not created. Could you look at it for me &
advise where I'm going wrong please? It is a combination of various code I've
found on the forum.

Dim dbs As Database
Dim tdf As TableDef
Dim fldNew As Field
Dim ind As Index

Set dbs = OpenDatabase(strBackendPath)
Debug.Print dbs

Set tdf = dbs.CreateTableDef(strNewCplsTbl)
Debug.Print tdf

tdf.Connect = ";DATABASE=" & strBackendPath & ""
Debug.Print tdf.Connect

tdf.SourceTableName = strNewCplsTbl
'db.TableDefs.Append tdf

Debug.Print tdf.SourceTableName

'This creates the table & appends the link to the FE.
'Just ensure the user has a way to refesh the link should his
'FE-to-BE relative path be different from yours.

With tdf
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection of your database.

'AutoNumber: Long with the attribute set.
Set fldNew = .CreateField("CoupleID", dbLong)
fldNew.Attributes = dbAutoIncrField + dbFixedField
.Fields.Append fldNew
.Fields.Append .CreateField("CoupleNumber", dbText, 4)
.Fields.Append .CreateField("MaleID", dbLong)
.Fields.Append .CreateField("FemaleID", dbLong)
.Fields.Append .CreateField("Ballroom", dbBoolean)
.Fields.Append .CreateField("Latin", dbBoolean)
.Fields.Append .CreateField("Active", dbBoolean)
.Fields.Append .CreateField("StartDate", dbDate)

End With
dbs.TableDefs.Append tdf
CurrentDb.TableDefs(strNewCplsTbl).RefreshLink

Set tdf = Nothing
Set dbs = Nothing
Set fldNew = Nothing
Set ind = Nothing

dbs.Close