Help with SQL statement

[OldEnough]

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.

 

[Dale Fye]

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

 

[OldEnough]

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]

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]

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]

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]

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]

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