Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Posts
    130

    Question Unanswered: using CurrentProject.Connection in an ADP

    I'm trying to append a table with ADOX to the SQL Server that an ADP is
    connected to but the CurrentProject.Connection won't allow me to:
    Code:
    Dim cat As New ADOX.Catalog
    Dim tbl As New ADOX.Table
    
    cat.ActiveConnection = CurrentProject.Connection
    
    tbl.ParentCatalog = cat
    
    tbl.NAME = "Table1"
    tbl.Columns.Append "Column1", adVarWChar, 50
    tbl.Columns.Append "Column2", adVarWChar, 100
    
    cat.Tables.Append tbl
    
    Set cat = Nothing
    Set tbl = Nothing
    I can hardcode cat.ActiveConnection like:
    Code:
    cat.ActiveConnection = "Provider=SQLOLEDB.1;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=...;Data Source=..."
    but I wanted to know if there was a way I could get the right string from
    the ADP. The CurrentProject.Connection is almost right, but I would
    have to change the string around:
    Code:
    ' CurrentProject.Connection = 
    ' "Provider=MSDataShape.1;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=...;Data Source=...;Data Provider=SQLOLEDB.1"

  2. #2
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Try using an ADODB.Recordset instead. ADOX is used mainly to view the structure of the database.

    Using .ActiveConnection = CurrentProject.Connection is fine in an Access ADP, provided the Access project is connected correctly to the SQL database

  3. #3
    Join Date
    Mar 2003
    Posts
    130
    Code:
    Dim cat As New ADOX.Catalog
    Dim tbl As New ADOX.Table
    
    cat.ActiveConnection = CurrentProject.Connection
    tbl.ParentCatalog = cat
    
    tbl.NAME = "Table1"
    tbl.Columns.Append "Column1", adVarWChar, 50
    tbl.Columns.Append "Column2", adVarWChar, 100
    
    ' Error on the next line
    cat.Tables.Append tbl
    
    Set cat = Nothing
    Set tbl = Nothing
    the error is:

    Run-time error '3251':

    Object or provider is not capable of performing requested operation.

    but if I hardcode the cat.ActiveConnection to the SQL Server, i.e.
    cat.ActiveConnection = "Provider=SQLOLEDB.1;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=...;Data Source=..."

    then I don't get the error.

    and I think you mean ADODB.Connection not ADODB.Recordset

    It's easier to use table and column objects rather than a
    CREATE TABLE (...) statement but I guess it will do.

Posting Permissions

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