Announcement

**E B Knoppert** · 7 Feb 2000, 07:21 AM

Why not use an empty DB? (template)

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

**Eric Pearson** · 7 Feb 2000, 07:30 AM

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).]

**Eric Pearson** · 7 Feb 2000, 07:49 AM

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).]

**jcfuller** · 7 Feb 2000, 09:40 AM

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

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

**Eric Pearson** · 7 Feb 2000, 09:50 AM

> 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>

**Randy Standridge** · 7 Feb 2000, 10:38 AM

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.

**jeroen brouwers** · 8 Feb 2000, 02:55 AM

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

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

**Guest** · 8 Feb 2000, 03:21 AM

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 )"
 
[b]After Executing the above statement sp_help Stuff produces:[/b]
 
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).]

**Eric Pearson** · 8 Feb 2000, 05:17 AM

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>

**Eric Pearson** · 8 Feb 2000, 05:20 AM

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>

**jeroen brouwers** · 9 Feb 2000, 08:59 AM

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

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

Announcement

Call VB from PBDLL (creating tables)

Call VB from PBDLL (creating tables)

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment