Import Records from and external database

[DIOS]

Im working on a database and im trying to import data from a external
database though VBA/DAO. ive tried to use ISERT INTO IN but cant seem
to get the syntax down right. Say Im working in db1 and tbl1 and have
some code to execute a SQL statement. I want to connect to an external
database db2 and tbl2. Now the fields are similar so i want to be able
to map what field gets what value.

I tried

sSQL = "INSERT INTO tbl1 " & _
"SELECT * " & _
"FROM tbl2 IN db2;"

where db2 is the full path to the external database. I cant seem to
get it to work and to map my fields. Any help is appreciated.

AGP

 

[John Spencer (MVP)]

You need to break out the value of db2. Try something like the following.

sSQL = "INSERT INTO tbl1 " & _
"SELECT * " & _
"FROM tbl2 IN '" & db2 & "' "

Your sSQL was creating a statement like:

INSERT INTO tbl1
SELECT * FROM tbl2 IN db2

When you wanted something more like

INSERT INTO tbl1
SELECT * FROM tbl2
IN 'C:\My Documents\myJetDb.mdb'

That is assuming db2 is a string containing
C:\My Documents\myJetDb.mdb

 

[prabha]

Hi,

Here's a way using DAO:

Dim db As DAO.Database
Dim sql As String

' Set the database to DB2 database.
Set db = OpenDatabase("full path to DB2")

sql = "INSERT INTO tbl1 IN 'full path name of current db' " & _
"SELECT tbl2.* FROM tbl2.*;"

db.Execute sql



You may want to check out also: ACC2000: How to Recover Data from a
Corrupted Table by Using the DAO Method
http://support.microsoft.com/default.aspx?scid=kb;en-us;815280

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."



--------------------
| From: (e-mail address removed) (DIOS)
| Newsgroups: microsoft.public.access.queries
| Subject: Import Records from and external database
| Date: 4 Feb 2004 08:17:43 -0800
| Organization: http://groups.google.com
| Lines: 17
| Message-ID: <[email protected]>
| NNTP-Posting-Host: 144.9.158.106
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1075911464 2508 127.0.0.1 (4 Feb 2004
16:17:44 GMT)
| X-Complaints-To: (e-mail address removed)
| NNTP-Posting-Date: Wed, 4 Feb 2004 16:17:44 +0000 (UTC)
| Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGXA06.phx.gbl!TK2MSFTNGXA0
5.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!newsfe
ed.gamma.ru!Gamma.RU!news.maxwell.syr.edu!postnews1.google.com!not-for-mail
| Xref: cpmsftngxa07.phx.gbl microsoft.public.access.queries:188667
| X-Tomcat-NG: microsoft.public.access.queries
|
| Im working on a database and im trying to import data from a external
| database though VBA/DAO. ive tried to use ISERT INTO IN but cant seem
| to get the syntax down right. Say Im working in db1 and tbl1 and have
| some code to execute a SQL statement. I want to connect to an external
| database db2 and tbl2. Now the fields are similar so i want to be able
| to map what field gets what value.
|
| I tried
|
| sSQL = "INSERT INTO tbl1 " & _
| "SELECT * " & _
| "FROM tbl2 IN db2;"
|
| where db2 is the full path to the external database. I cant seem to
| get it to work and to map my fields. Any help is appreciated.
|
| AGP
|

 

[DIOS]

I just cannot get this to work. i get a synatx error when i try to
execute.

Set db = ws.OpenDatabase("D:\Datafile\Company1999.mdb', True, True)

sSQL="INSERT INTO tblEmployees IN 'D:\Datafile\Company2004.mdb' SELECT
tblEmployees.* FROM tblEmployees.*;"

db.Execute sSQL


So my external database is Company1999.mdb and I want to import the
records from a table called tblEmployees to a table of the same name
in my new database Company2004.mdb.

I am running this code via VBA inside the Company2004.mdb so maybe
that has something to do with it. Also the old database is an
Access97 format where my new database is Access2000 format. I dont
know that that matters cause DAO should be able to read an Acccess97
and come up with a recordset I can insert into my new database.

AGP