Importing an ODBC database

[Eric]

I am trying to import 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 import the table instead of
creating a link table. I thought that we could just reimport the new data
every week. The problem is that when you import 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 import, import the tables with updated data and
then recreate the relationships in the new tables. Is there a way around
this?

I'd like to just reimport the data into the same table so that the office
people that dont' know about relationships dont' have to worry about
recreating that stuff everytime. Any suggestions?

Thanks.

 

[Michael Conroy]

Eric,
I have little experience with ODBC connections, but I do upload spreadsheets
and I run into the same problem. What I do is import the file into a holding
table, then use a make table query with the holding table as the source and
the table with relationships as the target. The query will delete the old
target and give you just the new information. I don't use relationships 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.

 

[ryguy7272]

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 Import routine. WARNING!!!
Try this on a copy of your database; YOU DO NOT WANT TO ACCIDENTALLY BE
DELETING TABLES!!!

Function Import()
On Error GoTo Import_Err

DoCmd.SetWarnings False

DoCmd.RunSQL ("DELETE * FROM [XXX your table name goes here XXX];")
DoCmd.TransferSpreadsheet acImport, 8, " XXX your table name goes here
XXX ", "C:\Documents and Settings\rms\Desktop\Forecasting\Up To
Access\Summary.xls", True, "XXX Excel Sheet Name XXX!A1:AA150"

DoCmd.SetWarnings True
'DoCmd.CloseForm "frmImport"


Import_Exit:
Exit Function

Import_Err:
MsgBox Error$
Resume Import_Exit

End Function

Regards,
Ryan---