Write to other DB

[DS]

I write this to a table in the current DB, I would also like to write this
to another DB that is not open nor linked. The other DB is called
DataSafe.mdb how would I do this?
Thanks
DS

Dim UPSQL As String
DoCmd.SetWarnings False
UPSQL = "UPDATE tblBackPath SET tblBackPath.BackName = " & Chr(34) &
Forms!frmBSRedundancy!TxtPath & Chr(34) & ", " & _
"tblBackPath.BackActive = Forms!frmBSRedundancy!ChkActive " & _
"WHERE tblBackPath.BackID = 1;"
DoCmd.RunSQL (UPSQL)
DoCmd.SetWarnings True

 

[NEWER USER]

I have used the following with success:

DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\PATH NAME\Name of
database.mdb", acTable, "tableNameFROM", "tableNameTO", False

 

[Chris O'C via AccessMonster.com]

Don't use RunSQL to run a query in VBA and don't use SetWarnings to turn off
notifications. Use the Execute method with the dbFailOnError argument and
error handling in the procedure, so that the user won't see any message
unless the query fails (exactly when you want the user to know something went
wrong), and the transaction will be rolled back. Otherwise, the RunSQL
method could fail and the user would never be alerted. Also, the SetWarnings
could accidentally be left turned off and other problems would result, such
as the user making changes to forms and not being prompted to save the
changes.

If your table in the other database has the same name, tblBackPath, and the
form frmBSRedundancy is open with the items filled out, then try this query
with the corrected path to the other database:

CurrentDb.Execute "UPDATE [;DATABASE=C:\PathToDB\DataSafe.mdb;].tblBackPath "
& _
"SET tblBackPath.BackName = " & Chr(34) & _
Forms!frmBSRedundancy!TxtPath & Chr(34) & _
", tblBackPath.BackActive = " & _
Forms!frmBSRedundancy!ChkActive & _
" WHERE tblBackPath.BackID = 1;", dbFailOnError


Chris
Microsoft MVP

 

[Dale Fye]

I noticed that in the line before the where clause, you included the
reference to chkActive inside the quotes, so I "fixed" that.

I also replaced your chr(34) stuff with a function (Quotes) that I use to
wrap text in quotes in SQL strings. I just find this easier to read, and it
takes less keystrokes as well. Additionally, if you want to wrap a date with
the # symbol, you can pass the function the lb symbol as the second
parameter, instead of the default ".

I believe you could also use:

UPSQL = "UPDATE tblBackPath IN 'C:\Temp\DataSafe.mdb' " _
& "SET tblBackPath.BackName = " _
& quotes(Forms!frmBSRedundancy!TxtPath) & ", " _
& "tblBackPath.BackActive = " Forms!frmBSRedundancy!ChkActive _
& " WHERE tblBackPath.BackID = 1;"

Public Function Quotes(TextToQuote as Variant, _
Optional WrapWith as string = """") as
string
'accepts a variant to handle NULLs and returns an empty string
'when a null is encountered

'If the TextToQuote value contains the WrapWith character
'embedded in the string, then the Replace function replaces a
'single occurance of that value with two of it.
Quotes = WrapWith _
& Replace(NZ(TextToQuote, ""), WrapWith, WrapWith & WrapWith) _
& WrapWith

End Function

HTH
Dale

 

[DS]

Thanks Chris,
It works great, also thank you for the advice.
DS

 

[Chris O'C via AccessMonster.com]

No prob. And did I mention that the Execute method usually runs your query
faster than RunSQL does? Another benefit.

Chris
Microsoft MVP

 

[DS]

Thanks, I like that Public Function Quotes...I will defintely use that.
Thanks
DS

 

[aaron_kempf]

Warning!

this Chris guy is not credible.
He does not know the basics of MS Access.

-Aaron