Re: Help with SQL statement
- From: Dale Fye <dale.fye@xxxxxxxxxx>
- Date: Tue, 6 May 2008 14:10:01 -0700
Old,
Just wanted to update you.
I've recently been advised that my comments about Access/Jet doing all of
the processing on the Front-end are incorrect. The amount of processing that
occurs on the front-end is tied very closely to the indexes that exist on the
table being queried, and whether the fields in your WHERE clause are indexed
or not.
--
HTH
Dale
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
"Dale Fye" wrote:
I've never used ADO with an Access backend, only with SQL Server, and the.
processes are totally different with Access and SQL Server backends. With
SQL Server, all of the processing is done on the server, with Access (DAO)
all of the processing is done in the front end. I believe that is also the
case with ADO when using an Access backend.
If you are working with very large files, then you should consider migrating
to SQL Server. SQL Server 2005 Express is free and will hold up to 4 GB in
each database.
Dale
"OldEnough" <OldEnough@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8A63440A-54DB-44D4-9EFD-411B85B2520E@xxxxxxxxxxxxxxxx
I am learning. I appreciate your comments. I took your suggestion and
worked
out the problem with my SQL statement. I am too much of a novice. My
connection string connected me with the backend and my maketable query
made a
new table in the backend instead of in my local database. I changed the
code
to change the destination file and solved the problem.
strSQL = "SELECT * INTO tblItems IN MylocalAppAddress FROM tblItems;"
I will use the append query version.
Is using linked tables good technique? I am struggling with my front end
apps bogging down over the network when certain forms (queries based on
large
tables) are left open. I thought by unlinking the tables , using ADO for
pulling data from the backend and for inserting new data into the backend
I
could improve performance. I don't know enough to judge. What is your
opinion?
"Dale Fye" wrote:
Old
While using ADO is a good technique, using linked table is just as
effective.
Is there a particular reason you don't want to use linked tables? I use
linked tables to backend databases (both Access and SQL Server) all the
time.
Dale
"OldEnough" <OldEnough@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6DC063B6-71FB-4F27-B443-7CF90613171F@xxxxxxxxxxxxxxxx
Dale
Thanks again for taking time with this. I haven't had time today to
experiment. I have just been running as hard as I can with other
problems.
I
will take your advice about deleting the existing records and appending
new
records to the existing tables. That has always worked for me in the
local
database. I am trying to use ADO so that I can reference a database on
the
server without linking files and with as little impact on the network
as I
can manage. I will attempt your recommended approach over the weekend
and
if
I can't manage a solution I may at least be able to ask better
questions
on
Monday.
Thanks again.
"Dale Fye" wrote:
So you have decided not to take my advice and do a delete and then an
append
to the local table?
If so, I think the way I would handle this is to link the backend
table
to
your database. When you do this, it will probably give it a new name
of
tblItems_1 (I would change that to tblItems_backend).
Then, I would delete the local table and refresh that tabledefs (to
prevent
potential conflicts of creating a new table with the same name as the
old
one):
docmd.DeleteObject acTable, "tblItems"
currentdb.Tabledefs.refresh
Then I would do the make table query:
Currentdb.Execute "INSERT * INTO tblItems FROM tblItems_Backend",
dbfailonerror
Another option would be to use the TransferDatabase method, which
basically
allows you to copy a table from one datasource to another. This would
avoid
the necessity to link to the backend.
BTW, using any of these techniques will cause problems if you have
relationships created which are based on any of the fields in
tblItems.
An
even better way would be to link to the backend, then update the local
table
with data that it doesn't already have, or which has been changed
since
the
last download.
HTH
Dale
--
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
"OldEnough" wrote:
Thanks Dale
The table names in the backend and local databases are
identical.
Does
that pose a problem for the SQL statement? I think this is one of
the
things
I tried.
SELECT * INTO tblItems FROM tblItems
I may have done something else wrong but I got an error about
missing
parameters, and the query failed.
"Dale Fye" wrote:
Old,
Instead of dropping the old table, I would recommend deleting it's
contents
and then replacing them with the contents of the table from the
backend. To
do this, you would need to create a DELETE query, and an Append
query.
All you currently have is a SELECT, but to do it the way you are
talking
about, you would need a Make-Table query which has a format:
SELECT * INTO local_Items FROM tblItems
HTH
Dale
--
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
"OldEnough" wrote:
I apologize if this is a foolish post. I have tried to figure it
out but I
haven't had much luck. I would like to overwrite a local table
by
pulling a
new recordset from our backend. Users have a local app with a
large
table
that needs to be updated once every few days.
ideally I would drop the original table : Docmd.runsql "Drop
Table
tblItems;"
Then replace it with a new table.
The code below pulls the correct recordset. Could someone
suggest
how to
modify it to replace the original table
Dim strSQL As String
Dim oCmd As Command
Dim oRS As New ADODB.Recordset
Dim cnn As New ADODB.Connection
AppPath = getTA_dbPath() 'function to get backend db path
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0;Data Source=" &
AppPath
End With
strSQL = "SELECT * FROM tblItems;"
Set oRS = New Recordset
With oRS
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
End With
Set oCmd = New Command
With oCmd
.CommandText = strSQL
.CommandType = adCmdText
.ActiveConnection = strConn()
Set oRS = .Execute
End With
oRS.Close
Set oRS = Nothing
Any suggestions would be appreciated.
- Prev by Date: Re: Displaying the Report's Name function
- Next by Date: RE: How to display a field into two colmuns?
- Previous by thread: Suppressing the # in a string if there's no data
- Next by thread: multiple reports
- Index(es):
Relevant Pages
|
