Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2002
    Posts
    2

    Unanswered: Insert data From MS Access to SQL SERVER Using OPENROWSET

    I am trying to Insert thr data from MS Access table to MS SQL Server using "OPENROWSET" .. But could not make it run...

    I am using VB 6.0 , ADO 2.7
    MS Access 2000 (User=Admin,Passward=password), MS SQL Server 2000..


    Follwing is my code... (Code in Red)
    Dim cnAccess As New ADODB.Connection
    Dim cnSQL As New ADODB.Connection
    Dim strSQL$

    strSQL = "Provider=sqloledb;" & _
    "Data Source=mlabserver2;Initial Catalog=dbDataTransfer;User Id=sa;Password=password;"

    cnSQL.ConnectionString = strSQL
    cnSQL.Open


    ' This code gives me
    'Run-time error '-2147217900(80040e4d)
    'Line 1: Incorrect syntax near ",".
    'With DSN
    cnSQL.Execute "SELECT * INTO tblTest2 " & _
    "FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','myDSN','C:\P rogram Files\Common Files\System\System.mdw','Admin','password'," & _
    "'SELECT * FROM tblTest')"


    ' This code gives me
    'Run-time error '-2147217900(80040e4d)
    'Line 1: Incorrect syntax near ",".
    'With out DSN
    cnSQL.Execute "SELECT * INTO tblTest2 " & _
    "FROM OPENROWSET('Provider=Microsoft.Jet.OLEDB.4.0'," & _
    "'Data Source=C:\Test.mdb'," & _
    "'Jet OLEDBystem Database=C:\Program Files\Common Files\System\System.mdw'," & _
    "'User Id=Admin'," & _
    "'Password=password'," & _
    "'SELECT * FROM tblTest')"


    ' THis code gives me
    'Server: Msg 7399, Level 16, State 1, Line 1
    'OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
    '[OLE/DB provider returned message: Cannot start your application. The workgroup 'information file is missing or opened exclusively by another user.]
    cnSQL.Execute "SELECT * INTO tblTest2 " & _
    "FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0'," & _
    "'C:\Test.mdb';" & _
    "'Admin';" & _
    "'password'," & _
    "'SELECT * FROM tblTest')"


    Please help me to solve this out. I am trying this for past 3 days but no luck.

    Thanks

  2. #2
    Join Date
    Jun 2002
    Posts
    2

    Still Problem

    Now it is working wth DSN

    Below is the working code

    cnSQL.Execute "SELECT * INTO tblTest5 " & _
    "FROM OPENROWSET('MSDASQL','CRS';'Admin';'password'," & _
    "'SELECT * FROM tblTest')"


    but I would not be having the DSN. I am trying with follwing code


    cnSQL.Execute "SELECT * INTO tblTest5 " & _
    "FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:\Test.mdb' ,'C:\Program Files\Common Files\System\System.mdw';'Admin';'password'," & _
    "'SELECT * FROM tblTest')"


    Problem is with workgroup file name. If I don't use User/Password then it is working but when I give user password then I need to give workgroup file also which it is not excepting...

    Any Idea

Posting Permissions

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