Using strings in make table query with RunSql command

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Need to make table in back end of a database where the path to the back end
may not always be the same. Path is stored in a table. Wanted to reference
the path to add in the sql statement either by using a string and dlookup or
from a field on a form. Neither apppear to work. Is there any way of doing
this. Is there a way of putting strings in when using the runsql command?
Many thanks
 
WJBR said:
Need to make table in back end of a database where the path to the back end
may not always be the same. Path is stored in a table. Wanted to reference
the path to add in the sql statement either by using a string and dlookup or
from a field on a form. Neither apppear to work. Is there any way of doing
this. Is there a way of putting strings in when using the runsql command?
Click to expand...


Check Help for the Execute method instead of using RunSQL.

To use a path to a different (unlinked) database table, you
should use code to construct an SQL statement that uses the
IN "path" phrase in the FROM clause. Check Help for details
on this option.
 
strSql = "SELECT CFAImport.F2 AS [Trans Number], CFAImport.F3 AS [Agent Ref],
CFAImport.F5 AS [OHG Ref], CFAImport.F6 AS Amount, CFAImport.F7 AS Payer,
Thank you for your response. It works if i put the path in but i would like
to use a string. Sql code below.

CFAImport.F8 AS RemAdviceTotal, CFAImport.F9 AS DateRemAdvice, CFAImport.F10
AS TrialBalanceMonth, CFAImport.F11 AS PaySlipTotal, CFAImport.F12 AS
SubBank, CFAImport.F13 AS [Note], CFAImport.F14, CFAImport.F15 " & _
"INTO [Cash from agents] IN [" & strDatabase & "] " & _
"FROM CFAImport;"
CurrentProject.Connection.Execute strSql

strDatabase is the string containing the path to the back end. As this is
likely to change depending on who is accessing the database, and is contained
in the local database, it would be nice if the sql could use the string to
use the correct path.
Many thanks
 
Queries do not allow for parameters in that context. That's
why I suggested using VBA to contruct the query by
concatenating the path into the SQL statement.

I suppose you could use a little more code to get the SQL
statement out of a QueryDef and use the Replace function to
cram the path into a dummy IN clause. Doesn't seem worth it
to me, but it would allow you to avoid embedding the SQL
statement in the procedure.

I didn't know you could use [ ] around the path, I've always
used " instead.
--
Marsh
MVP [MS Access]


strSql = "SELECT CFAImport.F2 AS [Trans Number], CFAImport.F3 AS [Agent Ref],
CFAImport.F5 AS [OHG Ref], CFAImport.F6 AS Amount, CFAImport.F7 AS Payer,
Thank you for your response. It works if i put the path in but i would like
to use a string. Sql code below.

CFAImport.F8 AS RemAdviceTotal, CFAImport.F9 AS DateRemAdvice, CFAImport.F10
AS TrialBalanceMonth, CFAImport.F11 AS PaySlipTotal, CFAImport.F12 AS
SubBank, CFAImport.F13 AS [Note], CFAImport.F14, CFAImport.F15 " & _
"INTO [Cash from agents] IN [" & strDatabase & "] " & _
"FROM CFAImport;"
CurrentProject.Connection.Execute strSql

strDatabase is the string containing the path to the back end. As this is
likely to change depending on who is accessing the database, and is contained
in the local database, it would be nice if the sql could use the string to
use the correct path.
Many thanks

Marshall Barton said:
Check Help for the Execute method instead of using RunSQL.

To use a path to a different (unlinked) database table, you
should use code to construct an SQL statement that uses the
IN "path" phrase in the FROM clause. Check Help for details
on this option.
Click to expand...
Click to expand...
 
Back
Top