Importing an ODBC database   

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.

date: Wed, 6 Aug 2008 08:59:03 -0700   author:   Eric

RE: Importing an ODBC database   

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.
-- 
Michael Conroy
Stamford, CT


"Eric" wrote:

> 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.

date: Wed, 6 Aug 2008 14:08:01 -0700   author:   Michael Conroy

RE: Importing an ODBC database   

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---

-- 
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 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.
> -- 
> Michael Conroy
> Stamford, CT
> 
> 
> "Eric" wrote:
> 
> > 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.

date: Mon, 24 Nov 2008 22:15:00 -0800   author:   ryguy7272