Set table conditions to run Docmd

[CWH via AccessMonster.com]

Hi;

I have a number of table transfers (import & export) I want to occur via code
when the fields in a master table are set specifically as such; (To execute
this I am using a command button on a form).

If Field Name “Number†=1 (data type = number) and a checkbox is set to yes
or -1 then

DoCmd.TransferDatabase export, Table1 etc…

If Field Name “Number†=2 and a checkbox is set to yes then

DoCmd.TransferDatabase export, Table2 etc…

And so forth…

This is what I have for code– but of course it doesn’t work


If [tbl_update]![Number] = 1 And [tbl_update]![Check] = -1 Then

DoCmd.TransferDatabase export, acTable, "TableName", "TableName"

Else
End if
Exit Sub

 

[Allen Browne]

You could set up your table with the names of the tables you might want to
export. Use a number if you wish, but include a field with the table name
too. You can then loop through the tables the user has checked for export.

Dim rs As DAO.Recordset
Dim strSql as String

strSql = "SELECT TblName FROM T1 WHERE IsPicked = True;"

Set rs =dbEngine(0)(0).OpenRecordset(strSql)
Do while not rs.EOF
DoCmd.TransferDatabase acExport, ...
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

 

[CWH via AccessMonster.com]

Hi Allen;

Thanks for your help. My initial request was incorrectly stated. I made a few
adjustments to the code you provided. As you can see when the command is
executed by the User it begins by importing a table called tbl_update. Next -
based on the selected tables within tbl_update (these are pre-selected) those
tables should be deleted and replaced with a new table with the same name.
I’m basically looking to delete the data in selected existing tables and
replace with updated data. The code works however it is replacing tables even
if the checkbox “Check†is not true. I’m sure I’m making more of this than
needs to be…

Dim rs As DAO.Recordset
Dim strSql As String

strFilter = ahtAddFilterItem(strFilter, "Access File (*.mdb)", "*.mdb")
strInputFileName = ahtCommonFileOpenSave(Filter:=strFilter, OpenFile:=True,
DialogTitle:="Select RMS Update File...", Flags:=ahtOFN_HIDEREADONLY)


DoCmd.TransferDatabase acImport, "Microsoft Access", strInputFileName,
acTable, "tbl_update", "tbl_update"

strSql = "SELECT TblName FROM tbl_update WHERE Check = True;"

Set rs = DBEngine(0)(0).OpenRecordset(strSql)
Do While Not rs.EOF

DoCmd.DeleteObject acTable, "ActivityCodes"
DoCmd.TransferDatabase acImport, "Microsoft Access", strInputFileName,
acTable, "ActivityCodes", "ActivityCodes"

‘Should I be listing all the tables here?’
rs.MoveNext

Loop
rs.Close
Set rs = Nothing

 

[Allen Browne]

Not sure what's going on, but CHECK is a reserved word in JET SQL
(kb248738), as well as in SQL Server and ODBC (kb125948.)

Perhaps you could try including the source table, and enclosing the reserved
name in square brackets:
strSql = "SELECT TblName FROM tbl_update WHERE tbl_update.[Check] =
True;"

If the tables are not being deleted successfully, you could DROP them:
db.Execute "DROP Table [Table1];", dbFailOnError

If there are relationships between the tables, it might not be possible to
DROP them while related tables exist.

If the tables are not changing structure, it might be easier to merely
delete the data and repopulate them. This is just an example, but
illustrates the kind of thing:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strTable as String
Dim strSql as String

Set db = CurrentDb()
Set rs = db.OpenRecordset("tbl_update", dbOpenDynaset)

Do while not rs.EOF
strTable = rs![TblName]
strSql = "DELETE FROM " & strTable & ";"
db.Execute strSql, dbFailOnError

strSql = "INSERT INTO " & strTable & " " & vbCrLf & _
"SELECT " & strTable & ".* " & vbCrLf & _
"FROM " & strTable & " IN 'C:\MyFolder\MyFile.mdb';"
db.Execute strSql, dbFailOnError
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

 

[CWH via AccessMonster.com]

Hi Allen;

hummm...

now I'm getting the following error;

Too few parameters. Expected <number>. (Error 3061)

Not sure what's going on, but CHECK is a reserved word in JET SQL
(kb248738), as well as in SQL Server and ODBC (kb125948.)

Perhaps you could try including the source table, and enclosing the reserved
name in square brackets:
strSql = "SELECT TblName FROM tbl_update WHERE tbl_update.[Check] =
True;"

 

[Allen Browne]

now I'm getting the following error;

Too few parameters. Expected <number>. (Error 3061)

That means there are 2 names in your query that Access does not recognise.