Loading external Data in Access Table - whats the most Efficient

[PhillWall]

Hi - New to Access here. I am looking at taking load off a Main DB Server by
moving certain functionality into Access. To do this, the first step is I
need to do is load data - via ODBC into a table.
I have the connection working and I have a passthrough query that does
exactly what I want. However it requires a parameter and I cant see a way of
executing via VBA a parameterised querydef directly into a table (Into a
record set - sure and then I can write the code to take each of the fields
and insert them manually row by row into the table but there a huge number
of fields and Im not convinced there isnt a better way of coding this)
I also have ADO and DAO code to run the SQL and load the recordset.
but again that means all this extra code to load the fields from the record
set and insert into the local table.

I dont think I can use INSERT INTO because the main query is running on an
external mySQL database not in the JET engine locally.

I can use VBA to change the querydef SQL and then run it using
DoCmd.OpenQuery "passthrough1", acViewNormal, acReadOnly.
That will actually do exactly what I want but in my readings I gather
frequent changing of Query SQLs will "bloat" the database So that means
after running these (there will be a large number of these - loading several
hundred rows or so each query) I would have to run a compact of the database.
That doesnt seem elegant either.

I cant use the execute method of querydef - which would allow for parameters
- because its a select query - not an update query.
I dont think Import will work because I dont want the whole table (which is
gigabytes in size )

I suspect Im not thinking "Access" because surely something so easy to do
manually should be as easy to do in VBA

SO WHAT I WANT TO DO is run an ODBC query against an external database and
load the results direectly into a table. (IF there is data in the table I
want to wipe that data - I can run a truncate on it easily enough but I
mention that incase that can be incorporated)

Whats the most efficent way to code this (Im more concerned with keeping the
code short and readable as the client will try to maintain this and the guy
who will do that is not a programmer - so Im asking for shorter simpler code
rather than the fastest executing code... - this process currently takes a
staff member ALL day and the db server is overloaded anyway so a sec or so
wont make a difference)

Here is my VBA ADO code that loads the recordset (this is just a proof of
concept thing so its a way simpler query than the real one)

SQLcmd = "select * from agent a where agent_id between " & ParmBeginDate &
" and " & ParmEndDate & " ;"

'Declare and Instantiate Connection to the server
Dim svrcnn As ADODB.Connection
Set svrcnn = New ADODB.Connection

'Open the connection
svrcnn.Open "Driver={MySQL ODBC 5.1
Driver};SERVER=192.168.X.XXX;UID=xxxxx;PWD=xxxxxxx;DATABASE=xxxxxx;PORT=3306"

MsgBox "Open the Record Set"
Dim svrrst As ADODB.Recordset
Set svrrst = New ADODB.Recordset


svrrst.Open SQLcmd, svrcnn

MsgBox "Print the contents"
Debug.Print "Test Debug Print"
Debug.Print svrrst.GetString

MsgBox "Close and Destroy the record set"
svrrst.Close
Set svrrst = Nothing

'Close the connection
svrcnn.Close

but as I say I want to load direct into a table. doCmd the Querydef does it
but I cant see how to pass the paramters.

Any suggestions on the best access way to do this appreciated

Phill

 

[david]

1) You don't have to compact every time - just now and then.
The amount of extra space used by a query plan is small.

2) It may not be elegant, but that's Access: it's a user controlled
system. There is no background process to do your database
compact for you unless you write it yourself.

3) DoCmd is a full-service Access wrapper for the Access
IDE. The IDE is a way of handling data without code, the
querydef has to be already saved so that you can click on
it with a mouse.

If you don't want bloat, you don't want saved objects,
you don't want to emulate using a mouse to run saved
objects.

4) ADO and DAO have methods for executing sql:
dao.dbengine.workspace.database.execute(SQL)

5) Use an ADO or DAO execute to execute INSERT
INTO against the target database.

6) The source does not have to be in the target
database, it can be a linked table, or a passthrough
query, or and ODBC connection string
FROM [database].[view or table]


(david)

 

[PhillWall]

Thanks for the replies = could I actually get some examples though.

:

1) You don't have to compact every time - just now and then.
The amount of extra space used by a query plan is small.

I have found the compact on close option which will probably resolve this
issue.
At this stage this is my best option.


2) It may not be elegant, but that's Access: it's a user controlled
system. There is no background process to do your database
compact for you unless you write it yourself.

See 1) above - compact on close is good enough I suspect - also I was
planning on writing VBA to do the compact - but give there is an option I can
turn on Ill probably just use that.

3) DoCmd is a full-service Access wrapper for the Access
IDE. The IDE is a way of handling data without code, the
querydef has to be already saved so that you can click on
it with a mouse.

If you don't want bloat, you don't want saved objects,
you don't want to emulate using a mouse to run saved
objects.

So I need an exmple of how to run in ADO or DAO - a SQL query to table
direct thich is what QUERYDEF does - or I have major code bloat to do what
query def lets me do in a few lines.

4) ADO and DAO have methods for executing sql:
dao.dbengine.workspace.database.execute(SQL)

AS far as I know how when I use execute I get a recordset = which is the
point of this posting. Can you point to any documentation or example code
that opens a table insstead of a recordset.

executing the SQL isnt difficult - its executing them Direct to a table.

5) Use an ADO or DAO execute to execute INSERT
INTO against the target database.

6) The source does not have to be in the target
database, it can be a linked table, or a passthrough
query, or and ODBC connection string
FROM [database].[view or table]

Again - I cant see how its possible to write code thats executing on a
different server (not locally) to use an an insert into a local table.
As its running on the mysql server = it cant see the local tables (and in
fact the SQL between access and mySQL is different enough that that if
Access sees the SQL - it errors,
The two SQL formats are incompatible. However If you can give an example of
code that will run a passthrough or ODBC query that does an insert into a
local table - great - I might be able to find a compatible subset (maybe) but
I cant see anyway to build it to run = can you give an exampe rather than
just saying it can be done. The SQL QUERY is in the original post

 

[david]

When you use DAO execute you never get a recordset.

I'm sure that ADO also has a method for executing procedural
SQL without returning a recordset. It is probably called execute,
but I don't use it.

Again - I cant see how its possible to write code thats executing on a
different server (not locally) to use an an insert into a local table.

You don't.

Like I said, you execute the INSERT against the local database.

Your local execute gets data from the different server. You do
that by specifying the source. The source does not have to be
in the local database.


You don't have to use a stand-alone SELECT to get data from
a different server.

Start by creating some dummy tables in a local database.

Execute an INSERT statement to copy the data from one
table to another table.

Once you have satisfied yourself that it is possible to execute
INSERT statements against your local database using local
tables, (for example, using a DAO execute statement), repost here.

application.currentdb.execute("INSERT INTO ....

(david)

Thanks for the replies = could I actually get some examples though.

:

1) You don't have to compact every time - just now and then.
The amount of extra space used by a query plan is small.

I have found the compact on close option which will probably resolve this
issue.
At this stage this is my best option.


2) It may not be elegant, but that's Access: it's a user controlled
system. There is no background process to do your database
compact for you unless you write it yourself.

See 1) above - compact on close is good enough I suspect - also I was
planning on writing VBA to do the compact - but give there is an option I
can
turn on Ill probably just use that.

3) DoCmd is a full-service Access wrapper for the Access
IDE. The IDE is a way of handling data without code, the
querydef has to be already saved so that you can click on
it with a mouse.

If you don't want bloat, you don't want saved objects,
you don't want to emulate using a mouse to run saved
objects.

So I need an exmple of how to run in ADO or DAO - a SQL query to table
direct thich is what QUERYDEF does - or I have major code bloat to do what
query def lets me do in a few lines.

4) ADO and DAO have methods for executing sql:
dao.dbengine.workspace.database.execute(SQL)

AS far as I know how when I use execute I get a recordset = which is the
point of this posting. Can you point to any documentation or example code
that opens a table insstead of a recordset.

executing the SQL isnt difficult - its executing them Direct to a table.

5) Use an ADO or DAO execute to execute INSERT
INTO against the target database.

6) The source does not have to be in the target
database, it can be a linked table, or a passthrough
query, or and ODBC connection string
FROM [database].[view or table]

Again - I cant see how its possible to write code thats executing on a
different server (not locally) to use an an insert into a local table.
As its running on the mysql server = it cant see the local tables (and in
fact the SQL between access and mySQL is different enough that that if
Access sees the SQL - it errors,
The two SQL formats are incompatible. However If you can give an example
of
code that will run a passthrough or ODBC query that does an insert into a
local table - great - I might be able to find a compatible subset (maybe)
but
I cant see anyway to build it to run = can you give an exampe rather than
just saying it can be done. The SQL QUERY is in the original post

(david)

 

[PhillWall]

Bear with me - but Im still missing the point here.

Its very easy to run insert into statements locally
e.g. INSERT INTO [currentdb().tableA] SELECT * from [currentdb().tableB]

THATS A trivial matter because its executed by Jet (or the new engine that
replaces Jet in 2007)
However the problem is I need to do
INSERT INTO [currentdb().tableA] SELECT * from [mysqldb.tableB]

and this SQL cant in anyway be seen by access or MS Access errors on the SQL
so its sent to the MYSQL DB - Access never sees the SQL thats being executed.
So if you cant let access see the SQL - how can you tell it to Insert into
something when you cant allow it to see the select part of the SQL (because
if you do - it fails with a SQL syntax error)

ARe you just saying this should work ? or have you tried it... The reason I
asked is I spent considerable time on this before I posted these questions
which is why IM asking for specific examples of code - Ive got a library of
Access books which Ive read plus read the posts here - Ive done a lot of
research and dont know how to do what you are saying.

Is the dao.dbengine.workspace.execute(SQL) method you talk about the
standard execute for update queries because thats the only execute I can find
doco on (Hence I asked for doco or specific code example)
I tried this well before I did this post and as I said in the first post. I
have get the SQL passed through to my SQL to execute inwhich case.. it cant
see hte local tables and I access sees the mySQL SQL it errors - so I cant
see how to put the two in an execute and not have it fail (It can fail
locally because it cant see the mySQLstuff or it can fail in ODBC because it
cant see the local tables but every attempts I make fails because of the
inherent incompatibility between the two.
I would appreciate a working example because I cant see how one Execute can
separate the two - arbitrarily as it were. Its not just a case of specifying
the database name and table name its a case of Access wont run MySQL SQL so
how so o stop it from parsing the select but understanding the Insert.


And can you explain the comment about DAO and not getting a record set. All
the books I have specifically use DAO recordsets - of which in DAO there are
three types - Dynaset, Table and Snapshot (Im using Snapshot)
So Im missing some subtle point your making as I can point you to many
chapters in many books on DAO data access and recordsets.

 

[david]

and this SQL cant in anyway be seen by access or MS Access errors on the

so its sent to the MYSQL DB - Access never sees the SQL thats being

That is your mistake. Don't send the INSERT statement to MYSQL.
If you want to insert data into an Access/Jet table, use DAO or ADO
to execute the INSERT statement in Access/Jet against the Access/Jet
mdb.

Yes I have scripted data extraction from SQL Server, Oracle,
Excel, Foxpro and Text databases.

No, never MYSQL, but enough to know what MYSQL does
not do.

Start by setting up a dummy extraction from a MDB table to
a MDB table. Once you have that working, we can discuss
how to set up a remote source.

If you were using Excel or SQ:L Server, there would be other
ways to do it. Since you are using MYSQL, this is how it works.

(david)

Bear with me - but Im still missing the point here.

Its very easy to run insert into statements locally
e.g. INSERT INTO [currentdb().tableA] SELECT * from [currentdb().tableB]

THATS A trivial matter because its executed by Jet (or the new engine that
replaces Jet in 2007)
However the problem is I need to do
INSERT INTO [currentdb().tableA] SELECT * from [mysqldb.tableB]

and this SQL cant in anyway be seen by access or MS Access errors on the
SQL
so its sent to the MYSQL DB - Access never sees the SQL thats being
executed.
So if you cant let access see the SQL - how can you tell it to Insert into
something when you cant allow it to see the select part of the SQL
(because
if you do - it fails with a SQL syntax error)

ARe you just saying this should work ? or have you tried it... The reason
I
asked is I spent considerable time on this before I posted these questions
which is why IM asking for specific examples of code - Ive got a library
of
Access books which Ive read plus read the posts here - Ive done a lot of
research and dont know how to do what you are saying.

Is the dao.dbengine.workspace.execute(SQL) method you talk about the
standard execute for update queries because thats the only execute I can
find
doco on (Hence I asked for doco or specific code example)
I tried this well before I did this post and as I said in the first post.
I
have get the SQL passed through to my SQL to execute inwhich case.. it
cant
see hte local tables and I access sees the mySQL SQL it errors - so I cant
see how to put the two in an execute and not have it fail (It can fail
locally because it cant see the mySQLstuff or it can fail in ODBC because
it
cant see the local tables but every attempts I make fails because of the
inherent incompatibility between the two.
I would appreciate a working example because I cant see how one Execute
can
separate the two - arbitrarily as it were. Its not just a case of
specifying
the database name and table name its a case of Access wont run MySQL SQL
so
how so o stop it from parsing the select but understanding the Insert.


And can you explain the comment about DAO and not getting a record set.
All
the books I have specifically use DAO recordsets - of which in DAO there
are
three types - Dynaset, Table and Snapshot (Im using Snapshot)
So Im missing some subtle point your making as I can point you to many
chapters in many books on DAO data access and recordsets.

 

[PhillWall]

Yes AS I mentioned in my last post - I had already done that.. thats trivial.
Its the next bit that I cant get to work insert from a mySQL source

 

[david]

There are a number of ways to refer to a remote source in Jet SQL.
The method you use depends a bit on what you are doing, and a bit
on what you are comfortable with.

Do you want to use a linked table? Do you want to use a pass-through
query? Or would you like to embed the source description in the
SQL of the INSERT procedure?

Post your sample SQL and I will give you some examples.

(david)

 

[sfisher973]

I have found the compact on close option which will probably resolve this
issue.
At this stage this is my best option.

Yeah, not recomended. If a compact goes bad it can through out the entire DB.