Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Sep 2005
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    is this ADO (2002)

    currentproject.openconnection, is this ADO?
    im disconnecting the connection so the ADP clears out all the tables, i then want to connect all the tables in another sql database to the adp. it does this when i use this command but not when i use the adodb.connection.........open
    am i doing something wrong? please bare with me im a complete beginner at this!

    i tried some code charlotte gave me (thanks) but it doesnt like this "Dim objTdf As ADOX.Table " or " Dim cat As ADOX.Catalog"

    "user defined type not defined" is the error i get

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: is this ADO (2002)

    Have you set a reference (in Tools | References... in the Visual Basic Editor) to Microsoft ADO Ext. 2.7 for DDL and Security? ADOX library requires a separate reference, besides the ADO reference (Microsoft ActiveX Objects 2.n Library)

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: is this ADO (2002)

    Yes, it is ADO. When you use that construction, you are specifically telling the application that you want to open a connection on the current project using the arguments you pass into the OpenConnection method. There really isn't any reason to use the other syntax.
    Charlotte

  4. #4
    New Lounger
    Join Date
    Sep 2005
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: is this ADO (2002)

    <img src=/S/bif.gif border=0 alt=bif width=70 height=28> thanks for your help guys i appreciate it very much being as im such a spanner at this. ive used the currentproject.openconnection to fill the adp with tables from the sql server, but they look like local tables. when i use ado to update a recordset the changes only take place in the ADP not on the server. What am i doing wrong now?
    <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: is this ADO (2002)

    Tables in an ADP are not linked as in an mdb. The ADP is a window into the SQL Server, so the tables appear local. I have no idea what you mean by the changes only taking place in the ADP because the ADP only reflects the SQL Server tables. Is the SQL Server on the same machine or on a network server? Without seeing the ADO code you're using to update the tables it isn't possible to identify the problem. Unless you give us specific information, there is no way to help you. <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>
    Charlotte

  6. #6
    New Lounger
    Join Date
    Sep 2005
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: is this ADO (2002)

    the srver is on the network and i connect to the server using this line

    CurrentProject.OpenConnection "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Hawkes;Data Source=COMPAQSERVER"

    i then run this code to see if it updates the table on the server.

    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.Open _
    CursorType:=adOpenForwardOnly, _
    LockType:=adLockOptimistic, _
    Source:="[Sales Ledger]", _
    ActiveConnection:=CurrentProject.Connection
    rst.AddNew
    With rst
    !SLCode = "TESTING"
    .Update
    End With

    when i run this test code and open the table in the adp the record has been added, but when i go onto enterprise manager and look in the database and open the table there it is still empty. Sorry if im a hinderence, im just a complete novice at ado and sql server.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: is this ADO (2002)

    The code you posted is leaky because you do not close and destroy the recordset object, but there's nothing else wrong with it. Although you could try running the update serverside by adding this line ahead of the rst.Open call

    rst.CursorLocation = adUseServer


    You have to be encountering a network/polling issue and you need to discuss it with the SQL DBA. You can test this by running the script to create the Northwind adp (assuming you have a local copy of SQL Server to play with), linking to it, and running a similar bit of code to add a record to one of its tables. If you do that, make sure you use the same kinds of permissions and roles as you use on the network SQL Server so you have a parallel situation.
    Charlotte

  8. #8
    New Lounger
    Join Date
    Sep 2005
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: is this ADO (2002)

    thanks for your help chralotte. you are right, there is something wrong with the network as when i run my application on the server it worked so thanks very much!

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: is this ADO (2002)

    Do you mean that it works when you add the CursorLocation line or that it works when you run the Access adp on the server? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Charlotte

Posting Permissions

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