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

 

[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

 

[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