Write to other DB

  • Thread starter Thread starter DS
  • Start date Start date
D

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
 
I have used the following with success:

DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\PATH NAME\Name of
database.mdb", acTable, "tableNameFROM", "tableNameTO", False
 
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
 
Warning!

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

-Aaron
 
Back
Top