Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am working to figure this out, but thought i'd run it by the loungers...

    i have a nifty pass-thru query that uploads an Access db to SQL Server:

    INSERT into dbo.ArchivedAccessDBs(AccessDB)
    SELECT * FROM OPENROWSET(BULK N'\\<ComputerName>\DB\sskelton\MyAccessDB.mdb', SINGLE_BLOB) as Document;

    I will need to programmatically specify the access db before running this to make sure I only upload the db's i just created and stored.

    Any ideas?

    TIA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can change the SQL property of the QueryDef object in code, see for example ACC2000: How to Simulate Parameters in an SQL Pass-Through Query.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='HansV' post='796615' date='06-Oct-2009 09:27']You can change the SQL property of the QueryDef object in code, see for example ACC2000: How to Simulate Parameters in an SQL Pass-Through Query.[/quote]
    In fact that is the approach we use when doing searches in a SQL Server backend and need to maximize performance. In those cases we typically use a UDF to pass the value through, and it provides subsecond response times.

    Steve, I'm still confused about what you are trying to achieve here. Storing an Access database inside a SQL Server database seems like a strange idea to me. And it appears you are using .MDB format databases rather than .ADP - is this some sort of application launcher where you are deploying the latest version of a front-end to workstations? I'm still puzzled.
    Wendell

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    What I am trying to do is, on the creation of a new access db, automatically store a copy in SQL Server for archiving purposes. I prefer to use SQL Server rather than a file system as it will be much more difficult for someone to 'mess' with the archived db.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='796615' date='06-Oct-2009 15:27']You can change the SQL property of the QueryDef object in code, see for example ACC2000: How to Simulate Parameters in an SQL Pass-Through Query.[/quote]


    I am trying to get a parameterized stored proc to work even as we speak. stuck in syntax-land...

    the TSQL that works:

    SELECT * FROM OPENROWSET(BULK N'\\<computername>\DB\sskelton\<static db name>', SINGLE_BLOB) as Document;

    with param:

    ALTER PROCEDURE [dbo].[InsertDB]
    @AccessDBName Varchar(50)
    --@BulkInsertString Varchar(255)
    -- Add the parameters for the stored procedure here
    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    INSERT into dbo.ArchivedAccessDBs(AccessDB)

    SELECT * FROM OPENROWSET(BULK N'\\<ComputerName>\DB\sskelton\' + @+AccessDBName '', SINGLE_BLOB) as Document;
    END

    I've tried embedding the @AccessDBName with N' + @AccessDBName + N' etc. but nothing seems to be working....
    I feel I am close, tho - just not quite there! As you can see, I may also buildout a string that is more detailed than just the Access name. Haven't tried that yet.

    <--------------TSQL fixed
    If you are interested I can post the code.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks all for your help! this ended up not requiring leveraging a pass-thru query. one little problem with the article cited: the code

    MyQ.sql = "sp_addgroup" & " " & NewGroup

    is intended to fire some SP with the parameter data as assigned to NewGoup. while this may be fine, what i ended up with was

    NewGroup = "<MyAccessDatabase>"

    quote = Chr(34)

    ' Set the SQL property and concatenate the variables.
    MyQ.SQL = "EXEC dbo.InsertDB" & " " & quote & NewGroup & quote

    Note, I had to wrap the value of the parameter in quotes to get the EXEC command to work.

    Another thing I had to battle was not being able to specify a UNC path in the parameter. Apparently SQL Server doesn't like \ in param values.

    Anyway, mission accomplished! Again, thanks for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •