Insert Into from a remote database - Need help with syntax

[khwaja]

I have the following code which I wish to use to empty an existing table 'main' first and then update it with the data from another database. I am not too sure if I am using the right syntax for the insert into code as far as a remote database is concerned. Could someone help me please?

Sub CopyEAData()
Dim dbs As Database, tdf As TableDef, strSQL As String
Set dbs = CurrentDb


DoCmd.SetWarnings False

DoCmd.RunSQL "Delete *from [Main];"

dbs.Execute " INSERT INTO Main SELECT * FROM \\nhomadgffs002\Share\EA variation\Data Files\EAVariationDatabaseData.mdb [Main];"

MsgBox "Update of Design Variation Approvals was also successfull."
Set dbs = Nothing


Cheers

AK

Note: Using Access 97 - still.

 

[gusbrunston]

Dear AK:

Only because there's no other reply to your thread...

If it's important to accomplish what you want to do in VBA code, then sorry, I don't know what the code might be.

But I accomplish deletion and rebuilding of tables in several parts of a db with a "make-table" query. The "make-table" query deletes the records, and rebuilds the table with the fresh data selected by the query with the same structure as the original table.

Once the query is designed and functioning properly, the code is simply

Dim stDocName As String
stDocName = "myMakeTableQuery"
DoCmd.OpenQuery stDocName

It seems to me that it would be a lot simpler to design the query than it would be to write the code.

[tt]Gus Brunston - Access2000(DAO)
Skill level based on 1-10: 7, on the way to 6, and beyond!
Webmaster: www.rentdex.com[/tt]

 

[cmmrfrds]

The only thing I can see wrong it lack of dot on Main table. I usually do this type of thing by linking the remote table to the local mdb then you can treat it like a local table. It will change the name to Main1 since you already have a table named Main.

dbs.Execute "INSERT INTO Main SELECT * FROM \\nhomadgffs002\Share\EA variation\Data Files\EAVariationDatabaseData.mdb.Main;"

I assume the fields line up in the select list with the table that is receiving the records.

 

[khwaja]

Thanks cmmrfrds. I fixed it bit it still returns syntax error in From clause. Will apreciate if you could compare it yoour code.

Gus, as the other table is remote, I would pefer to import it by using the solution cmmrfrds has had a look. Thanks for your suggestion.

Cheers

AK

Note: Using Access 97 - still.

 

[cmmrfrds]

Assuming the server name is nhomadgffs002

Maybe the spaces in names is causing problem.
Could try putting the whole name in ' quotes
'\\nhomadgffs002\Share\EA variation\Data Files\EAVariationDatabaseData.mdb.Main'

OR.
\\nhomadgffs002\Share\[EA variation]\[Data Files]\EAVariationDatabaseData.mdb.Main

Also,
here is another way to do it.
Dim sql1 As String, exterDB As String
exterDB = "C:\AEmptyDir\employee.mdb"
sql1 = "select * from employees IN '" & exterDB & "';"

Try getting the basic select to work first and then add the insert.
exterDB = "\\nhomadgffs002\Share\EA variation\Data Files\EAVariationDatabaseData.mdb"
SELECT * FROM Main IN '" & exterDB & "';"

 

[khwaja]

Thanks again for helping me. Unfortunately, none of these approaches work. I got run time error 3125 by using quotes. error 3131 (errors in from clause) by using square brackets. The third approach did not work as the following line is highlighted red as being incoorect.

SELECT * FROM Main IN '" & exterDB & "';"

Any further insights?


Cheers

AK

Note: Using Access 97 - still.

 

[Edski]

Try this:

Dim strSQL As String
strSQL = "INSERT INTO myTable IN '" & myDB & "' SELECT * FROM myTable"
CurrentDb.Execute strSQL

 

[cmmrfrds]

dbs.Execute = "SELECT * FROM Main IN '" & exterDB & "';"

Is this what you did?

 

[khwaja]

I still have the compile error to deal with before I see the result. I am getting error on the dbs.execute part of the following line.

dbs.Execute = "SELECT * FROM Main IN '" & exterDB & "';"

In case I am not doing anything wrong, I have reproduced the full code for your review. I am not too sure if need a SQL1 variable.


Sub CopyData()


Dim dbs As Database, tdf As TableDef, strSQL As String
Set dbs = CurrentDb
Dim sql1 As String, exterDB As String

DoCmd.SetWarnings False

exterDB = "\\nhomadgffs002\Share\EA variation\Data Files\EAVariationDatabaseData.mdb"
dbs.Execute = "SELECT * FROM Main IN '" & exterDB & "';"

DoCmd.RunSQL "Delete *from [Main];"

dbs.Execute "INSERT INTO Main SELECT * FROM '\\nhomadgffs002\Share\EA variation\Data Files\EAVariationDatabaseData.mdb.Main';"

MsgBox "Update of Design Variation Approvals was also successfull."

Set dbs = Nothing

End Sub

Cheers

AK

Note: Using Access 97 - still.

 

[cmmrfrds]

Sorry, I had an = in the syntax which was wrong.

dbs.Execute "SELECT * FROM Main IN '" & exterDB & "';"

 

[khwaja]

Thanks and sorry to bother you again. I am now getting a run time error 3065 (cannot execute a select query).

Cheers

AK

Note: Using Access 97 - still.

 

[Edski]

khwaja,
Try it with a local database (i.e. one that isn't on the server). If it works then the problem is probably with the server path. If it still doesn't work then the problem lies elsewhere. Doing things like this helps to narrow down the problems.

By the way,

DoCmd.RunSQL "Delete *from [Main];"

is missing a space after the *

Cheers.

 

[Edski]

Also,
The Execute method doesn't work with Select queries. You could delete that line anyway, since it does absolutely nothing!
Cheers.

 

[khwaja]

Thanks fior pointing this out. I tried a local/hard address and the result is still the same. It has probelm with running a select query.

Cheers

AK

Note: Using Access 97 - still.

 

[Edski]

khwaja,
You're code above works fine for me after I fixed the little errors. Can you get it to work yet?
Edski

 

[khwaja]

I am sorry but I am not understanding. If I were to take the dbs.excute part, then what would be the oprimnary instruction for the code. Could you please copy and paste the code with necessary amendment as you are suggesting?

Cheers

AK

Note: Using Access 97 - still.

 

[Edski]

Sub CopyData()
Dim dbs As Database
Dim exterDB As String

    Set dbs = CurrentDb

    DoCmd.SetWarnings False
    
    exterDB = "\\nhomadgffs002\Share\EA variation\Data Files\EAVariationDatabaseData.mdb"
   
    DoCmd.RunSQL "Delete * FROM Main"

    dbs.Execute "INSERT INTO Main SELECT * FROM " & exterDB & ".Main"
        
    MsgBox "Update of Design Variation Approvals was also successfull."
    
    Set dbs = Nothing

End Sub

 

[khwaja]

Sorry my friend for bothering you on two different front and realy appreciate your help. I am not having a good day. I might just call it a day. I ran the code and I still have the syntax error in FROM clause (error 3131) on the following line. Not sure what to do.

dbs.Execute "INSERT INTO Main SELECT * FROM " & exterDB & ".Main"


Cheers

AK

Note: Using Access 97 - still.

 

[Edski]

Hi AK,
I really don't know what's wrong. I'm using Access 97 and it works fine. You should have the DAO 3.6 Object Library referenced (since Access doesn't complain when you declare dbs. I can only guess that it doesn't like the path to the database on the server. Can you double check again with the code above using a local database in the same directory as the one you are using?

Also, is EAVariationDatabaseData.mdb written in Access 97? I guess it should be otherwise you would get a 3343 Error (unrecognized database format). I seem to have run out of advice. :-(

 

[StarPassing]

Try changing the following:

dbs.Execute "INSERT INTO Main SELECT * FROM " & exterDB & ".Main"

To:

Dbs.Execute “INSERT INTO MAIN SELECT * FROM MAIN IN ‘” & exterDB & “’”, dbFailOnError

Works for me.