By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,678 Members | 2,377 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,678 IT Pros & Developers. It's quick & easy.

Have SQL syntax for create table/data, how run it in Access2003 ?

P: n/a
Jan
Have an SQL create/import script. Running this on SQL would make it
create a table with some values. Can Access2003 somehow use such and
SQL script?

I went into SQL query view to try it out, but the amount of data was
too big to paste in according to Access.

Sample data:
--
-- PostgreSQL database dump
--

\connect - admin

SET search_path = public, pg_catalog;

--
-- TOC entry 2 (OID 5994920)
-- Name: post_code_areas; Type: TABLE; Schema: public; Owner: admin
--

CREATE TABLE post_code_areas (
gid serial NOT NULL,
plz99 character varying,
plz99_n integer,
plzort99 character varying,
the_geom geometry,
CONSTRAINT "$1" CHECK ((srid(the_geom) = 4326)),
CONSTRAINT "$2" CHECK (((geometrytype(the_geom) =
'MULTIPOLYGON'::text) OR (the_geom IS NULL)))
);
--
-- Data for TOC entry 5 (OID 5994920)
-- Name: post_code_areas; Type: TABLE DATA; Schema: public; Owner:
admin
--

INSERT INTO post_code_areas VALUES (0, '01067', 1067, 'Dresden',
'SRID=4326;MULTIPOLYGON(((13.7189358 51.07600017,13.72121532
51.07498173,13.72244724 51.07323078,13.7243034 51.07141494,13.72696308
51.06893157,13.727583 51.06863403,13.72820184 51.06833649,13.72943988
51.06772611,13.73232744 51.06452562,13.734207 51.06212235,13.735017
51.06047436,13.73702796 51.05793384,13.74013044 51.05562588,13.74254496
51.05455776,13.7458782 51.05422593,13.74720372 51.0541992,13.7485332
51.05418399,13.74983892 51.05443194,13.74999336 51.05278782,13.749156
51.05128095,13.74727536 51.04925919,13.74523056 51.04711917,13.74415668
51.04710774,13.74180228 51.04713825,13.74138936 51.04733274,13.74040116
51.04750059,13.73867604 51.04737477,13.73614596 51.04700847,13.73446188
51.04652022,13.7320452 51.0457878,13.7287674 51.04431531,13.72675608
51.04329678,13.72481424 51.04403685,13.7236212 51.04520037,13.72306356
51.04571535,13.72291452 51.04584504,13.72249044 51.04622646,13.72220604
51.04804608,13.72205052 51.04877472,13.72213944 51.05075076,13.72145472
51.05113218,13.72060692 51.05215458,13.71866148 51.05284497,13.71706884
51.05348208,13.70457252 51.0561333,13.69833084 51.05745693,13.6967706
51.05778885,13.695984 51.0579567,13.69715976 51.06051252,13.69621764
51.06446073,13.69720476 51.06465531,13.6991664 51.06504438,13.70181096
51.06680676,13.70330424 51.06938175,13.70382516 51.0714378,13.70634264
51.0742836,13.70990268 51.07570263,13.71315852 51.0761871,13.716117
51.07649616,13.7189358 51.07600017)))');
INSERT INTO post_code_areas VALUES (1, '01069', 1069, 'Dresden',
'SRID=4326;MULTIPOLYGON(((13.74983892 51.05443194,13.75114716
51.05468754,13.7536344 51.05537415,13.75719084 51.05694195,13.76096148
51.05319975,13.75954524 51.04895778,13.75887672 51.04775997,13.7584494
51.046920

May 13 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
"Jan" <ja*********@hotmail.com> wrote in
news:11**********************@i40g2000cwc.googlegr oups.com:
Have an SQL create/import script. Running this on SQL would
make it create a table with some values. Can Access2003
somehow use such and SQL script?

I went into SQL query view to try it out, but the amount of
data was too big to paste in according to Access.
You will need to edit it, to clean up things Access doesn't need
to know about, convert to Access-allowed datatypes, and to
separate the Create Table statement from the Insert Into
statements: Access wants separate SQL queries, that end with a
semicolon.

Access doesn't have a geometry type field, and that would need
to be simulated.

It would probably be easier to manually create the table and get
a delimited text file to import.

Sample data:
--
-- PostgreSQL database dump
--

\connect - admin

SET search_path = public, pg_catalog;

--
-- TOC entry 2 (OID 5994920)
-- Name: post_code_areas; Type: TABLE; Schema: public; Owner:
admin -- CREATE TABLE post_code_areas (
gid serial NOT NULL,
plz99 character varying,
plz99_n integer,
plzort99 character varying,
the_geom geometry,
CONSTRAINT "$1" CHECK ((srid(the_geom) = 4326)),
CONSTRAINT "$2" CHECK (((geometrytype(the_geom) =
'MULTIPOLYGON'::text) OR (the_geom IS NULL)))
);
--
-- Data for TOC entry 5 (OID 5994920)
-- Name: post_code_areas; Type: TABLE DATA; Schema: public;
Owner: admin
--

INSERT INTO post_code_areas VALUES (0, '01067', 1067,
'Dresden', 'SRID=4326;MULTIPOLYGON(((13.7189358
51.07600017,13.72121532 51.07498173,13.72244724
51.07323078,13.7243034 51.07141494,13.72696308
51.06893157,13.727583 51.06863403,13.72820184
51.06833649,13.72943988 51.06772611,13.73232744
51.06452562,13.734207 51.06212235,13.735017
51.06047436,13.73702796 51.05793384,13.74013044
51.05562588,13.74254496 51.05455776,13.7458782
51.05422593,13.74720372 51.0541992,13.7485332
51.05418399,13.74983892 51.05443194,13.74999336
51.05278782,13.749156 51.05128095,13.74727536
51.04925919,13.74523056 51.04711917,13.74415668
51.04710774,13.74180228 51.04713825,13.74138936
51.04733274,13.74040116 51.04750059,13.73867604
51.04737477,13.73614596 51.04700847,13.73446188
51.04652022,13.7320452 51.0457878,13.7287674
51.04431531,13.72675608 51.04329678,13.72481424
51.04403685,13.7236212 51.04520037,13.72306356
51.04571535,13.72291452 51.04584504,13.72249044
51.04622646,13.72220604 51.04804608,13.72205052
51.04877472,13.72213944 51.05075076,13.72145472
51.05113218,13.72060692 51.05215458,13.71866148
51.05284497,13.71706884 51.05348208,13.70457252
51.0561333,13.69833084 51.05745693,13.6967706
51.05778885,13.695984 51.0579567,13.69715976
51.06051252,13.69621764 51.06446073,13.69720476
51.06465531,13.6991664 51.06504438,13.70181096
51.06680676,13.70330424 51.06938175,13.70382516
51.0714378,13.70634264 51.0742836,13.70990268
51.07570263,13.71315852 51.0761871,13.716117
51.07649616,13.7189358 51.07600017)))'); INSERT INTO
post_code_areas VALUES (1, '01069', 1069, 'Dresden',
'SRID=4326;MULTIPOLYGON(((13.74983892 51.05443194,13.75114716
51.05468754,13.7536344 51.05537415,13.75719084
51.05694195,13.76096148 51.05319975,13.75954524
51.04895778,13.75887672 51.04775997,13.7584494 51.046920


--
Bob Quintal

PA is y I've altered my email address.
May 14 '06 #2

P: n/a
As Bob points out JET SQL is JET SQL and your SQL is not JET SQL. So
you need to do a lot of defining and mapping.

If there is some way, perhaps ODBC, that you can link to this SQL Table
you could try
DBEngine(0)(0).Execute "SELECT * INTO NewTable FROM SQLTable"
JET will create NewTable and take its best guess at Field Types while
naming Fields as Expr1000, Express1001 etc. I have no idea what it will
do with something like the Geometry Field, but you could find out.

Of course afterwards you woul have to rename your fields, establish
your foreign keys. triggers, validity rules, indexes, but it might be
an interesting beginning.

May 14 '06 #3

P: n/a
Jan
Unfortunately all I have is a 2MB file with the top portion of it
listed above. Are there any obvious cleanups I can do, or do I need to
install SQL Server and first import it there and then do text export or
ODBC?

And if I do a cleanup, how would I make Access read the data as it's
too huge for the query SQL windows?

May 14 '06 #4

P: n/a
"Jan" <ja*********@hotmail.com> wrote in
news:11**********************@u72g2000cwu.googlegr oups.com:
Unfortunately all I have is a 2MB file with the top portion of
it listed above. Are there any obvious cleanups I can do, or
do I need to install SQL Server and first import it there and
then do text export or ODBC?

And if I do a cleanup, how would I make Access read the data
as it's too huge for the query SQL windows?

If you study the information you presented you will see that it
is several SQL statements, each of which does a portion of the
task, The first is a create table statement.Do that first. The
others are insert into statements, from the data I've seen each
is for 1 record.

Once the table is created, you could perhaps write a VBA module
to read a portion of the file, programatically fix the SQL to
comply with the Access rules, and execute the statement.

What do you want to do with this data once you have it in
Access?

The reason I ask is because it's apparently data for a
geographic mapping aplication, and useless unless you intend to
(re-)write the entire application. I doubt you understand the
complexity of that task.
--
Bob Quintal

PA is y I've altered my email address.
May 14 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.