none
Import table to Access 03 from TopSpeed RRS feed

  • Question

  • (Apologize if this is the wrong forum- please let me know)

     

    I am attempting to import/link a table from a topspeed database into Access 2003.  I have created a TopSpeed dsn to do this.  With Excel 2003 it works fine:  I use the "Import External Data" feature, select my dsn, the tables in the database come up, and it gets loaded onto the worksheet.  However, when I use the import or link feature in Access, after selecting "ODBC Databases" and my DSN, I get (before the table selection menu):

     

    "Reserved Error (-7778); there is no message for this error."

     

    Also noteworthy is that I can use the connection string of the DSN in visual basic and establish recordsets in the TopSpeed database, but I get the same error if I attempt to link or use TransferDatabase in VB. 

     

    This is the connection string of my DSN:

     

            "ODBC; " _
            "DRIVER={SoftVelocity Topspeed driver (read-only) (*.tps)}; " _
            "DBQ=C:\TestDB.phd\! " _
            "NullEmptyStr=N; " _
            "Oem=N; " _
            "Extension=phd; "

     

     

    Very clueless at the moment.

    Wednesday, November 26, 2008 8:56 PM

All replies

  • If you can connect via ADO have you tried importing via code using a SELECT INTO statement?

    Thursday, November 27, 2008 4:11 AM
  • Thanks, I will try to look into that when I return to work on Monday.

    Friday, November 28, 2008 4:28 PM
  •  Paul P Clement IV wrote:

    If you can connect via ADO have you tried importing via code using a SELECT INTO statement?

     

    I guess I'm a little confused as to how to attempt that.  Right now, the main thing I can do codewise with VB is create a recordset with an adodb connection.  Is it possible to execute queries such as the above with an adodb connection, across different databases? 

     

    I have made some progress manually importing tables from the topspeed database into Access.  Turns out I can import okay when I create a user dsn that specifies the topspeed file.  However, if I create a user dsn (using the odbc administrative tool) and leave the the database field blank, it will not work.  Even though I am prompted to specify the file if I choose this DSN, I then get my original error.  Any idea why?  I would think once I specify the file, it should work as well as the other dsn.  The reason this concerns me is I am trying to move to automating this process without having to make a file-specific DSN each time. 

    Monday, December 1, 2008 5:17 PM
  • Below is an example of a SQL string which imports from SQL Server to Access. Other than the ODBC argument you should be able to replace the SQL Server connection string with a TopSpeed version. Your connection of course would be to the Access database and you would run the query using the ExcecuteNonQuery method.

     

    SELECT * INTO Orders FROM [Orders] IN '' [ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes]

     

    I don't know much about TopSpeed but I think the DSN-less connection string would look something like the following:

     

    DRIVER={Topspeed ODBC Driver};DBQ=C:\FilePath\;PWD=;

    Tuesday, December 2, 2008 7:12 PM
  • Thanks again immensely for the help. 

     

    For awhile there I could not figure out why you had one double-quote in the middle of your query.  I thought maybe you had forgotten out the closing quotation at the end, and was pulling my hair out putting double quotes and brackets all over the place trying to make it work.  Eventually, I realized that it was actually two single quotes.  Computers really drive me insane. 

     

    Anyways, I've tried a few different methods and I think your approach is the simplest.  However, as with my other attempted methods, it works only when the odbc connection string references an existing user DSN.  I'm still puzzled that I can establish an adodb connection variable in VB with the dsn-less topspeed string (see first post), so it obviously is a valid string, but using it for these import procedures always gives me one of two unspecified errors (-7778 and -7711). 

     

    Since the connection string apparently has to contain a DSN, my next attempt is to have a stored user DSN, then in the code edit its database parameter in the registry to the desired location, before it is called.

    Wednesday, December 3, 2008 3:48 PM