How many of you use Access against a DB2 backend

[jeep2001]

How do you simulate the "make table command" in DB2.
I was trying a pass thru with insert into....and unsuccessful.

 

[dhookom]

A pass-through query must be written with the exact syntax of the "remote" database. I'm not sure what the syntax is for creating a new table with SQL in DB2 but it might not be the same as Access or any other database.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

You have to create the table with SQL like below:

CREATE TABLE LIBRARY_NAME.TABLENAME (
	PKFIELD DECIMAL(5, 0) NOT NULL DEFAULT 0 , 
	LASTNAME CHAR(30) NOT NULL DEFAULT '' , 
	FIRSTNAME CHAR(25) NOT NULL DEFAULT '' ,
	STREET1 CHAR(25) NOT NULL DEFAULT '' , 
	STREET2 CHAR(15) NOT NULL DEFAULT '' , 
	CITY CHAR(20) NOT NULL DEFAULT '' , 
	STATE CHAR(2) NOT NULL DEFAULT '' , 
	ZIPCODE CHAR(9) NOT NULL DEFAULT '' , 
	HMPHONE CHAR(10) NOT NULL DEFAULT '', 
	PRIMARY KEY( PKFIELD ) )

(the DEFAULT value is not required as shown in the code below)

if you want your table to have an autonumber primary key you use:

CREATE TABLE LIBRARYNAME.TABLENAME (
	PKFIELD INTEGER GENERATED ALWAYS AS IDENTITY (
	START WITH 1 INCREMENT BY 1
	NO MINVALUE NO MAXVALUE 
	NO CYCLE NO ORDER 
	CACHE 20 ) ,
	QUESTNTXT CHAR(200) NOT NULL ,
	REPRTTXT CHAR(200) NOT NULL , 
	RPTSECTION CHAR(50) NOT NULL ,
	CONSTRAINT LIBRARYNAME.Q_LIBRARY_TABLE_PKFIELD_00001 PRIMARY KEY( PKFIELD ) )

then you can run a standard insert query

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design