Why does this SQL not work?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Patrick Fisher
    #1

    Why does this SQL not work?



    Hi All
    The function below is intended to create a table called tblNewTable
    which is a copy of tblProducts in another database, the name of the
    database is held in strFileName.

    The problem is that the function below refuses to treat strFileName as
    a string


    Function CopyDB(strFileN ame)
    Dim x As String
    x = DLookup("[DataPath]", "CurrentUse r")
    Dim strSQL As String
    Dim Db As Database
    Set Db = CurrentDb
    strSQL = "SELECT tblProducts.* INTO tblNewTable IN "
    strSQL = strSQL & strFileName
    strSQL = strSQL & "FROM tblProducts;"
    Db.Execute strSQL, dbFailOnError
    End Function

    Thanks in advance
    Patrick
    Tags: None
    • Peter Russell
      #2
      Re: Why does this SQL not work?

      Looks OK on the surface.

      What exactly are you passing via strFileName?
      Does it have any spaces in it? If so put square brackets round the whole
      path.

      Peter Russell


      Patrick Fisher previously wrote:
      [color=blue]
      >
      > Hi All
      > The function below is intended to create a table called tblNewTable
      > which is a copy of tblProducts in another database, the name of the
      > database is held in strFileName.
      >
      > The problem is that the function below refuses to treat strFileName as
      > a string
      >
      >
      > Function CopyDB(strFileN ame)
      > Dim x As String
      > x = DLookup("[DataPath]", "CurrentUse r")
      > Dim strSQL As String
      > Dim Db As Database
      > Set Db = CurrentDb
      > strSQL = "SELECT tblProducts.* INTO tblNewTable IN "
      > strSQL = strSQL & strFileName
      > strSQL = strSQL & "FROM tblProducts;"
      > Db.Execute strSQL, dbFailOnError
      > End Function[/color]

        Comment

        • MacDermott
          #3
          Re: Why does this SQL not work?

          Try adding a space in front of FROM.
          The way you have it, your destination string will run together with FROM.

          HTH

          "Peter Russell" <rusty@127.0.0. 1> wrote in message
          news:memo.20041 122111208.1912C @russellscott.b tinternet.com.. .[color=blue]
          > Looks OK on the surface.
          >
          > What exactly are you passing via strFileName?
          > Does it have any spaces in it? If so put square brackets round the whole
          > path.
          >
          > Peter Russell
          >
          >
          > Patrick Fisher previously wrote:
          >[color=green]
          > >
          > > Hi All
          > > The function below is intended to create a table called tblNewTable
          > > which is a copy of tblProducts in another database, the name of the
          > > database is held in strFileName.
          > >
          > > The problem is that the function below refuses to treat strFileName as
          > > a string
          > >
          > >
          > > Function CopyDB(strFileN ame)
          > > Dim x As String
          > > x = DLookup("[DataPath]", "CurrentUse r")
          > > Dim strSQL As String
          > > Dim Db As Database
          > > Set Db = CurrentDb
          > > strSQL = "SELECT tblProducts.* INTO tblNewTable IN "
          > > strSQL = strSQL & strFileName
          > > strSQL = strSQL & "FROM tblProducts;"
          > > Db.Execute strSQL, dbFailOnError
          > > End Function[/color]
          >[/color]


            Comment

            Previous template Next
            Working...