Results 1 to 4 of 4
  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've been looking around for this and thought i'd ask the lounge. basically, i want to do strSQL = "SELECT * INTO dbo_SQLSERVERTABLE FROM ACCESSTABLE;" so as to send all records at one go to SQL Server. i don't want to maintain ODBC on the target machines or have a linked table that exists any longer than necessary. so, is there a way to do this in VBA?

    1. link into a SQL Server table (without relying on a local ODBC file)
    2. run the SQL
    3. drop the link

    i've seen various solutions using tabledefs or even OPENROWSET but i got a 'cannot find ISAM' error on one attempt. I could do something tedious like a loop and multiple insert statements but yuck. anyhow, i'm pretty sure this is doable but a bit foggy on the road to take.

    TIA

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Bottom line answer is no - the only way to link to a SQL Server table is to use ODBC. You would be better off to create an import package in SQL Server and pull the data in that way. And with that approach you can schedule it to occur at specific dates and times. Of course if the Access database is on a laptop and is only connected at random times, that approach won't work reliably. I suspect that you will need to use the approach Andrew suggests in his reply.
    Wendell

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I know of No way to Run an Insert Into Query where the Source or the Target is SQL Server directly from Access.
    You could do it with loops.


    Alternatively you could create a temporay ODBC link to the SQL Server table.
    Run the query against the local table and then drop the linked SQL table.


    Code:
    Function LinkSQLRS()
    
    Dim tdf As DAO.TableDef
    
    Const CONSQLSVR = "ODBC;Driver=SQL SERVER;SERVER=YourServerName;UID=YourUserID;PWD=YourPassword;Database=YourSQLDatabase;"
    
    Set tdf = CurrentDb.CreateTableDef("tmpLocalTableName")
    tdf.Connect = CONSQLSVR
    tdf.SourceTableName = "SQLTableName"
    CurrentDb.TableDefs.Append tdf
    
    'Do You SQL Here
    'Using the Local ODBC Table Name
    'etc etc........
    
    
    'Kill the temporary linked SQL ODBC Table
    CurrentDb.TableDefs.Delete ("tmpLocalTableName")
    
    End Function
    If you wanted you could also set the Hidden Property of the New Local ODBC table so it would not be seen.
    Another way to make it not visible is to call it UsysTableName (unless Hidden Objects are on permanent view.
    Andrew

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by AndrewKKWalker View Post
    I know of No way to Run an Insert Into Query where the Source or the Target is SQL Server directly from Access.
    You could do it with loops.


    Alternatively you could create a temporay ODBC link to the SQL Server table.
    Run the query against the local table and then drop the linked SQL table.


    Code:
    Function LinkSQLRS()
    
    Dim tdf As DAO.TableDef
    
    Const CONSQLSVR = "ODBC;Driver=SQL SERVER;SERVER=YourServerName;UID=YourUserID;PWD=YourPassword;Database=YourSQLDatabase;"
    
    Set tdf = CurrentDb.CreateTableDef("tmpLocalTableName")
    tdf.Connect = CONSQLSVR
    tdf.SourceTableName = "SQLTableName"
    CurrentDb.TableDefs.Append tdf
    
    'Do You SQL Here
    'Using the Local ODBC Table Name
    'etc etc........
    
    
    'Kill the temporary linked SQL ODBC Table
    CurrentDb.TableDefs.Delete ("tmpLocalTableName")
    
    End Function
    If you wanted you could also set the Hidden Property of the New Local ODBC table so it would not be seen.
    Another way to make it not visible is to call it UsysTableName (unless Hidden Objects are on permanent view.

    you can definately run insert into from Access to SQL Server when the table you are doing the INSERT INTO is linked:
    strSQL = "SELECT * INTO <Access Table> FROM dbo_<SQL Server Table as Linked Object> WHERE OWNERSHIP_VENDOR_NO = '" & VendorNumber & "' AND LVL_BEG_DT BETWEEN #" & FromDate & "# AND #" & ToDate & "#;"
    DoCmd.RunSQL strSQL

    A very nice thing as you don't need to loop. It's a set-based operation. I hope the temporary ODBC link/unlink provides the same functionality - will try it.

    Thanks!

Posting Permissions

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