Announcement

Collapse
No announcement yet.

Call VB from PBDLL (creating tables)

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Call VB from PBDLL (creating tables)

    Hope someone can help:

    I'm using an Access-db (no choices here!), meaning that the ODBC does not support 'Create Table' and 'Alter Table'. VB itself has no problem with it. So my idea was to call a VB ? (i guess a dll but what / how exactly?) that handles it for me.

    The question is: how do I make a vb dll/exe (?) and how do I call it from PBDLL?

    thanks-J.Brouwers

  • #2
    Why not use an empty DB? (template)



    ------------------

    Comment


    • #3
      Jeroen --

      Since you are a SQL Tools user...

      Your SQL Tools package includes an empty MDB file for exactly that purpose. In your \SQLTools directory (or wherever you installed it) look for BlankDatabases.EXE. It is a self-extracting archive file, so when you run it, it will create a subdirectory with an empty Access database.

      If you are trying to add a table to an existing database...

      CREATE TABLE is part of the "core" syntax that all ODBC databases are required to support, so I would be astonished if Access did not support it. It may or may not support ALTER TABLE -- I'm not sure -- but if not, it should be possible to use CREATE TABLE and other SQL statements to "clone" an existing table and add or change a column at the same time.

      The core ODBC syntax is usually sufficient to "simulate" things like ALTER TABLE if a certain database does not support it directly.

      As far as VB "DLLs" go, as I understand it, they are not true Windows DLLs and they can only be called by VB programs. Somebody may correct me...

      -- Eric

      ------------------
      Perfect Sync: Perfect Sync Development Tools
      Email: mailto:[email protected][email protected]</A>



      [This message has been edited by Eric Pearson (edited February 07, 2000).]
      "Not my circus, not my monkeys."

      Comment


      • #4
        Adding to my response...

        I just checked, and Microsoft Access supports both CREATE TABLE and ALTER TABLE.

        I found the syntax of both functions listed in Access Database Design and Programming by Steven Roman, published by O'Reilly, ISBN 1-56592-297-2.

        -- Eric

        ------------------
        Perfect Sync: Perfect Sync Development Tools
        Email: mailto:[email protected][email protected]</A>



        [This message has been edited by Eric Pearson (edited February 07, 2000).]
        "Not my circus, not my monkeys."

        Comment


        • #5
          I have used PB/DLL and ODBC to create and alter tables in an Access
          Db. The one thing you cannot do is Create a db. The db needs to exist.

          James


          ------------------

          Comment


          • #6
            > The db needs to exist.

            ...which is why we supply the blank database. Just make a copy of the MDB file, open it, and use CREATE TABLE to your heart's content.

            -- Eric

            ------------------
            Perfect Sync: Perfect Sync Development Tools
            Email: mailto:[email protected][email protected]</A>

            "Not my circus, not my monkeys."

            Comment


            • #7
              BTW, VB dlls are based on COM so they are not "callable" by PB/DLL unless you use a 3rd party product like JazzAge (or if you like to torture yourself by writing OLE API calls).



              ------------------
              R.

              Comment


              • #8

                Thanks for all the response. Apparently I’m doing something wrong. The SQL TOOLS are included in my PBDLL, so this should work, no?

                The DSN that we use to open the DB (called KB1.mdb) looks like this.
                DSN:
                [ODBC]
                DRIVER=Microsoft Access Driver (*.mdb)
                UID=admin
                UserCommitSync=Yes
                Threads=3
                SafeTransactions=0
                PageTimeout=5
                MaxScanRows=8
                MaxBufferSize=2048
                FIL=MS Access
                DriverId=281
                DefaultDir=C:\Sparker\Includes
                DBQ=C:\Sparker\Includes\KB1.mdb

                In de Drivers-section of the ODBC Data Source Manager it says the Access driver is version 4.00.3711.08 using odbcjt32.dll

                This is not de DLL that is provided with SQL TOOLS but all the DLL’s that are provided are actually in my Windows Sytem directory. Yet, when using the ODBC Manager, there’s no choice in chosing anything but the Jet Engine. Maybe that’s the problem?

                Anyway, here’s the code which does not provide the desired respons.


                PB Code:

                SUB TEST1
                SQL_Init
                IF SQL_DBIsOpen THEN
                ' PRINT #1, "DB Open"
                ELSE
                ' PRINT #1, "Opening DB"
                SQL_OpenDB "C:\test.Dsn"
                SQL_ErrorClearAll
                END IF

                LOCAL SQL$
                SQL$ = "CREATE TABLE Index5 (ID %SQL_CHAR, FIELD1 %SQL_CHAR)
                CALL SQL_1(SQL$)
                END SUB

                SUB SQL_1(SQLSt AS STRING)
                ON ERROR GOTO SQL_1_Error

                LOCAL result&
                result& = SQL_Stmt(%IMMEDIATE, SQLSt)
                IF result& <> %SUCCESS AND result& <> %SUCCESS_WITH_INFO THEN
                SQL_ErrorClearAll
                SQLST = "Error!"
                END IF
                EXIT SUB
                SQL_1_Error:
                SQL_ErrorClearAll
                SQLST = "Error!"
                END SUB


                Hope you can help. Thanks in advance.

                J. Brouwers



                ------------------

                Comment


                • #9
                  Shouldn't you use sql syntax in your create table statement?
                  A char field needs the max number of bytes specified.

                  I don't fool with access but use several sql server engines.

                  Code:
                   
                  Sql$ = "CREATE TABLE Stuff ( id  char(4) NOT NULL, _
                                               name varchar(40) NULL, _
                                               shoesize int NOT NULL )"
                   
                  After Executing the above statement sp_help Stuff produces:
                   
                  Name                                                                                                                                                                                                                                                            Owner                                                                                                                                                                                                                                                           Type                                                           Created_datetime            
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------- -------------------------------------------------------------- --------------------------- 
                  Stuff                                                                                                                                                                                                                                                           dbo                                                                                                                                                                                                                                                             user table                                                     Feb 8 2000  7:20AM          
                    
                  Column_name          Type       Computed   Length      Prec  Scale Nullable   TrimTrailingBlanks FixedLenNullInSource 
                  -------------------- ---------- ---------- ----------- ----- ----- ---------- ------------------ -------------------- 
                  id                   char       no         4                       no         yes                no                   
                  name                 varchar    no         40                      yes        yes                no                   
                  shoesize             int        no         4           10    0     no         (n/a)              (n/a)                
                    
                  Identity                                                                                                                                                                                                                                                        Seed                                       Increment                                  Not For Replication 
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------- ------------------------------------------ ------------------------------------------ ------------------- 
                  No identity column defined.                                                                                                                                                                                                                                     (null)                                     (null)                                     (null)              
                    
                  RowGuidCol                                                                                                                                                                                                                                                      
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------- 
                  No rowguidcol column defined.                                                                                                                                                                                                                                   
                    
                  Data_located_on_filegroup                                                                                                                                                                                                                                       
                  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------- 
                  PRIMARY                                                                                                                                                                                                                                                         
                    
                  The object does not have any indexes.
                    
                  No constraints have been defined for this object.
                    
                  No foreign keys reference this table.
                  ------------------
                  Ron

                  [This message has been edited by Ron Pierce (edited February 08, 2000).]

                  Comment


                  • #10
                    Jeroen --

                    Ron is exactly right. When you do this...

                    Code:
                    SQL$ = "CREATE TABLE Index5 (ID %SQL_CHAR, FIELD1 %SQL_CHAR)
                    ...you are sending this literal string to the SQL/ODBC driver...

                    Code:
                    CREATE TABLE Index5 (ID %SQL_CHAR, FIELD1 %SQL_CHAR)
                    The driver (and therefore Microsoft Access) only understands the SQL language. It does not understand PowerBASIC "equates" or variables, or SQL Tools values like %SQL_CHAR, at least not directly. When you are using PowerBASIC and SQL Tools functions, you must use equates and variables and familiar things like that. But when you are creating a SQL Statement for Microsoft Access (or any database), you must use "pure" SQL syntax that the SQL/ODBC driver can understand. So in order to create a table called Index5 with two CHAR (i.e. fixed-length string) columns called ID and FIELD1 you would have to do something like this...

                    Code:
                    SQL$ = "CREATE TABLE Index5 (ID CHAR(4) NOT NULL, FIELD1 CHAR(32) NOT NULL)"
                    The (4) and (32) tell the driver how wide the fixed-length fields should be. You could also use VARCHAR instead of CHAR, to produce variable-length-string columns, but they are not as efficient for short strings. The NOT NULL clauses tell the database not to allow "missing" values in those columns.

                    Generally speaking, when you have a problem like this, it can be very helpful to examine the SQL/ODBC error message(s) that will inevitably be generated. The code you posted simply discards this valuable information (via the SQL_ErrorClearAll function) and reports "Error!", which is not very helpful. Try doing something like this instead...

                    Code:
                    IF SQL_Error THEN
                       MSGBOX SQL_ErrorQuickAll
                    END IF
                    ...so that the error messages will be displayed. The SQL_ErrorQuickAll is a quick way (hence the name) to examine and and clear the error stack in one step.

                    In the case of the code that you posted, I would expect a Microsoft Access error message like "Invalid column specification" or something like that. And that tells you where to start looking!

                    Also, I noticed that your table is called Index5. If you are really trying to create an index (as opposed to a table) you would need to use the CREATE INDEX statement instead of CREATE TABLE.

                    Hope this clears things up...

                    -- Eric


                    ------------------
                    Perfect Sync: Perfect Sync Development Tools
                    Email: mailto:[email protected][email protected]</A>

                    "Not my circus, not my monkeys."

                    Comment


                    • #11
                      Ron --

                      If you could edit your most recent post so that the lines aren't so
                      long, it would make the rest of the messages much easier to read.

                      -- Eric

                      ------------------
                      Perfect Sync: Perfect Sync Development Tools
                      Email: mailto:[email protected][email protected]</A>

                      "Not my circus, not my monkeys."

                      Comment


                      • #12
                        Thanks (I should add 'again' to Eric).

                        I overlooked that completely. Thanks for the input, gonna try it right away. I let you know if it works. I'm getting better (sometimes slowly apparently).

                        Jeroen

                        ------------------

                        Comment

                        Working...
                        X