Imported tables vs Linked 1

[teqtaq]

I have Reporting tables (views) that are getting updated nightly and linked into Access.mdb
Running a query using these Reports takes me a 35 min when linked.
When I have these tables Imported and local - query running in under 2sec.

Is there way I can write some function that will go to the location using ODBC and import these tables for me daily right after update?

thanks

 

[PWise]

to what are these tables linked (access,sqlserver,oracal)??

look @ docmd.transferxxxx

 

[PWise]

also make table queries

 

[SoCalAccessPro]

Here's how I do that:

Private Sub Form_Open(Cancel As Integer)


Dim msg As String
Dim rsp

msg = "Do you want to updat/refresh the data ?"
rsp = MsgBox(msg, vbYesNo, "Update Data")

If rsp = vbYes Then
    
    DoCmd.DeleteObject acTable, "Table to be deleted"
    DoCmd.TransferDatabase acImport, "ODBC Database", _
        "ODBC;DSN=YourDSN;UID=User;PWD=PW;LANGUAGE=English;" _
        & "DATABASE=Your SQL DB", acTable, "Your Table", "Your Local Table"
Else
End If

End Sub

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[teqtaq]

Thanks so much, I am off to try this! Yahoo!

 

[teqtaq]

I've taken out
DoCmd.DeleteObject acTable, "Table to be deleted"

and I am getting table Linked instead of Imported. Any suggestions? Thanks!

 

[teqtaq]

Also if I need to Import 6 tables, do I have to repeat the code for each?

 

[teqtaq]

Also what is required in "DATABASE=Your SQL DB"?

 

[SoCalAccessPro]

I delete the table because if I don't, Access automatically appends a _1 to the table name, which renders any queries I have useless.

Yeah, you'd just do it for each table. When I have to do things like this more than once, I store the table neames in an array and loop through the array.

Also what is required in "DATABASE=Your SQL DB"?

That would be the name of the Database on the SQL server. I assumed you're inporting from SQL server.


Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[teqtaq]

I am importing from Oracle.
However I did adjustment to the code but I do not understand which table (if any) suppose to be present in Access before code execution?
The reason for this question is when I hit
acTable, "Your Table", "Your Local Table"
part - I am getting error that says MS jet engine can not find "Your Local Table" - check the name blah blah...

Is the "Your Local Table" even suppose to BE there?
I thought "Table to be deleted" is the same as
"Your Local Table"...

Little confused. Thanks

 

[SoCalAccessPro]

Your Local Table" should be replaced with what you want Access to name the table being imported.

"Your Table" is the name of the table on the server.



Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[teqtaq]

I understand except why is the error message says
MS jet engine can not find the object "TEST"
Make sure the object exists or check if object exists and if you spelled the name correctly.

I renamed "Your Local Table" into "TEST"

So if "Your Table" will be renamed into "TEST" - why does the jet engine looking for the "TEST"?

thanks

 

[SoCalAccessPro]

Can you post your code ?

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[teqtaq]

Private Sub Form_Open(Cancel As Integer)


Dim msg As String
Dim rsp

msg = "Do you want to updat/refresh the data ?"
rsp = MsgBox(msg, vbYesNo, "Update Data")

If rsp = vbYes Then

DoCmd.DeleteObject acTable, "ReportTable"

DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN=dsnname;UID=username;PWD=password;SERVER=servername", acTable, "ReportTable123", "ReportTable"

End If
End Sub
_________________________

Thanks

If ReportTable does not exists - it says MS jet engine can not find "ReportTable"
If ReportTable exists it says MS jet engine can not find "ReportTable1"

So at least I am glad it gets to the end of the code...

Thanks

 

[SoCalAccessPro]

As you've found, "ReportTable" has to exsist, or the 1st docmd.DeleteObject method will fail. So, make sure it exsists before running your code.

I'm baffled why you are getting those error. Break out of the code entirely, perhaps compact and repair your DB, and try it again.

Perhaps we should check and see if REportTable exsistsw before deleting it.




Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[teqtaq]

I had tried running it without deleting table first and the same error happened.

It is not clear to me in
acTable, "ReportTable123", "ReportTable"
why is it looking for the "ReportTable" when it is not even suppose to exist yet.

Than a mistery IF "ReportTable" exists - why is it trying to make add 1 to the name and looking for "ReportTable1"?

I was laying and made "ReportTable1" and error said it is not finding "ReportTable11"
_______________________________________________

I had compacted and ran again. I had changed name of non exisitng table to "TEST" and now error says it can not find object "TEST" because of the object not exists or name misspelled...

Isn't it frustrating?

Anyway thanks so much for trying to help, if there is anything else I can do...