This post isn’t very old, so I’ll post my response (I think really old
posts
are not as likely to be read). Simply run a macro, like that below, to
delete all data from your table before you do the Im****t routine.
WARNING!!!
Try this on a copy of your database; YOU DO NOT WANT TO ACCIDENTALLY BE
DELETING TABLES!!!
Function Im****t()
On Error GoTo Im****t_Err
DoCmd.SetWarnings False
DoCmd.RunSQL ("DELETE * FROM [XXX your table name goes here XXX];")
DoCmd.TransferSpreadsheet acIm****t, 8, " XXX your table name goes here
XXX ", "C:\Do***ents and Settings\rms\Desktop\Forecasting\Up To
Access\Summary.xls", True, "XXX Excel Sheet Name XXX!A1:AA150"
DoCmd.SetWarnings True
'DoCmd.CloseForm "frmIm****t"
Im****t_Exit:
Exit Function
Im****t_Err:
MsgBox Error$
Resume Im****t_Exit
End Function
Regards,
Ryan---
--
RyGuy
"Michael Conroy" wrote:
> Eric,
> I have little experience with ODBC connections, but I do upload
spreadsheets
> and I run into the same problem. What I do is im****t the file into a
holding
> table, then use a make table query with the holding table as the source
and
> the table with relation****ps as the target. The query will delete the
old
> target and give you just the new information. I don't use relation****ps
that
> much but I think they are maintained with this method.
> The other way is a two step process. Append the primary key into the
target
> table, using an append query. This will get a full list of all old and
new
> records. Then udate all the data fields with an update query. Hope this
helps.
> --
> Michael Conroy
> Stamford, CT
>
>
> "Eric" wrote:
>
> > I am trying to im****t data from quickbooks pro. I have qodbc and can
get to
> > the information fine however when I create a link table, the very
large
> > tables cause access 2007 to crash. I decided to im****t the table
instead of
> > creating a link table. I thought that we could just reim****t the new
data
> > every week. The problem is that when you im****t a table that already
exists
> > it appends a number after the new table. I would have to delete the
existing
> > talbes that I am going to im****t, im****t the tables with updated data
and
> > then recreate the relation****ps in the new tables. Is there a way
around
> > this?
> >
> > I'd like to just reim****t the data into the same table so that the
office
> > people that dont' know about relation****ps dont' have to worry about
> > recreating that stuff everytime. Any suggestions?
> >
> > Thanks.
|
