Query an external database...

  • Thread starter Thread starter DIOS
  • Start date Start date
D

DIOS

Im working within an Access2000 database and writing code in VBA. What
i want to do is connect to an Access97 database through DAO and query
a table and import those records into my Access2000 database. The
tables have the same name and are similar in structure except they
have different names for fields. Ive been trying this mehtod but i
just cant seem to get it right:

Set db = ws.OpenDatabase(dbOld, True, True)

sSQL = "INSERT INTO tblGroup IN '" & CurrentDb.Name & "' " & _
"SELECT tblGroup.* FROM tblGroup.*;"

db.Execute sSQL

What am I doing wrong?

AGP
 
I've never used IN, so I don't really know. But, I saw your previous post
about this, and saw that there was no answer there either, so I'll give you
something to work with. So, these are an "if nothing else" answer.

1. Link to the table, then perform the query without the IN
2. If you can't link, then open a recordset based on the previous data. Loop
through the recordset and write the values to the destination table(another
recordset).

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
DIOS said:
Im working within an Access2000 database and writing code in VBA. What
i want to do is connect to an Access97 database through DAO and query
a table and import those records into my Access2000 database. The
tables have the same name and are similar in structure except they
have different names for fields. Ive been trying this mehtod but i
just cant seem to get it right:

Set db = ws.OpenDatabase(dbOld, True, True)

sSQL = "INSERT INTO tblGroup IN '" & CurrentDb.Name & "' " & _
"SELECT tblGroup.* FROM tblGroup.*;"

db.Execute sSQL

What am I doing wrong?

AGP
Click to expand...

If you don't want to simply link to the external database, you can use
code like this:

sSQL = _
"INSERT INTO tblGroup " & _
"SELECT tblGroup.* FROM tblGroup " & _
"IN '" & dbOld & "';"

CurrentDb.Execute sSQL, dbFailOnError

Note: the above assumes that dbOld is a *string* containing the full
path and name of the external .mdb file you want to pull records from.
That's what I gathered from your example, though the prefix "db"
normally implies a Database object.
 
That was my last resort...to query the database and keep the recordset
in memory and then cycle through it and dump the contents into my
current table. However, there must be an easier way to do this as I
have like 10 tables to import.

Thanx for the suggestions.

AGP
 
1) If the two databases have the same name, they must be
in different folders, ie you can't just have different
extensions for the two databases.

2) You can't use * if the field names are different.

3) You can connect to queries this way, as well as
to tables, so you can create a query in either database
to do the field name translation.

4) There is no easy way to do the field name translation
in Jet SQL: you can't use *, you can't use Field(2), etc.

5) You can use the field index if you use DAO in VBA
instead of SQL:
for each fld in tbl2 do: tbl1.field(i) = fld: i=i+1:

6) Jet also accepts [databasename].[tablename]

(david)
 
Phil said:
I think the field name has to be the same.
Click to expand...

Why do you think so? In the query designer it is possible to select one
field in the top row, and another in the destination row.

OP: did you try linking the 97-table, and creating a local INSERT INTO
statement?
 
¤ Im working within an Access2000 database and writing code in VBA. What
¤ i want to do is connect to an Access97 database through DAO and query
¤ a table and import those records into my Access2000 database. The
¤ tables have the same name and are similar in structure except they
¤ have different names for fields. Ive been trying this mehtod but i
¤ just cant seem to get it right:
¤
¤ Set db = ws.OpenDatabase(dbOld, True, True)
¤
¤ sSQL = "INSERT INTO tblGroup IN '" & CurrentDb.Name & "' " & _
¤ "SELECT tblGroup.* FROM tblGroup.*;"
¤
¤ db.Execute sSQL
¤
¤ What am I doing wrong?

There is no other alternative other than to specify each field name (from both tables) in the SQL
statement for your import (or export).


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
I think that almost works except that my fields are named differently
so Im trying this and it doesnt seem to work. I can feel im close, i
just have to get the synatax right:

sSQL = "INSERT INTO tblGroup (FullName, Initials) " & _
"SELECT EmpNme, Init" & _
"FROM tblGroup " & _
"IN '" & sOldDBPath & "';"

CurrentDb.Execute sSQL


Its almost driving me insane. I know this should be a pretty easy
query but i just cant get it to work right. Ive looked at the help and
ive searche don google.

AGP
 
DIOS said:
I think that almost works except that my fields are named differently
so Im trying this and it doesnt seem to work. I can feel im close, i
just have to get the synatax right:

sSQL = "INSERT INTO tblGroup (FullName, Initials) " & _
"SELECT EmpNme, Init" & _
"FROM tblGroup " & _
"IN '" & sOldDBPath & "';"

CurrentDb.Execute sSQL


Its almost driving me insane. I know this should be a pretty easy
query but i just cant get it to work right. Ive looked at the help and
ive searche don google.
Click to expand...

You must get in the habit of stating exactly what error message you get,
or how the outcome is different from your expectations, instead of
simply saying "it doesn't seem to work." In this case, the only thing I
see that is still wrong in the statement you posted is that you have no
space after the field name "Init". Try this corrected version:

sSQL = "INSERT INTO tblGroup (FullName, Initials) " & _
"SELECT EmpNme, Init " & _
"FROM tblGroup " & _
"IN '" & sOldDBPath & "';"

and report the result.
 
I apologize. The error reported is a synatx error in SQL statement.
I must have skipped that space after the Init field. let me try that
and see how it turns out.

AGP
 
Well, I reviewed the SQL and it looks like so:

sSQL = "INSERT INTO tblEmployees (FullName, Initials) " & _
"SELECT EmpNme, Init " & _
"FROM tblEmployees " & _
"IN '" & sOldDBPath & "';"

CurrentDb.Execute sSQL

When I run this inside my Access VBA code I get the error:
Runtime error 3134
Syntax error in INSERT INTO statement.

There has to be a way of doing this. It seems like a simple query to
me but I just cant get the right syntax down to work properly.

AGP
 
In the Access 2K file, create a link to the Access 97 table
you'd like to import from. Name the link:

linkEmployees

Create a Query in the Access 2K file. Name the
sQlinkEmployees

Save and close the query. Make sure it's named
sQlinkEmployees.

Double - click on it to make sure the records retrieved are
correct.

Right click on it and open it in design mode. We're going to
change it to a MakeTable query now and rename it.

Right click inside the table area and click on the QueryType,
change it from Select to Make Table. After you do this, you
should get a prompt for a new table name.

When it asks for the new table name, name the table:
tblEmployees

After it is given a name, save the query itself under a new name.
Name it:
0001-mtq-tblEmployees

The reason I put 0001 on the front is because if it's the first
query in a set of queries, you'll be able to know what it's doing,
how many queries there are, etc.

After it's saved under the new, name, double-click on it.

It should ask for confirmation and ask if you are sure you'd like
to create the new table.

Once you click yes, you'll have an extremily fast table creation
mechanism. You can call the db.Execute methods on the query
name and your done.

This creates a fresh new table within Access every time.

Hope this helps.

Dim db As DAO.Database
Dim strSQL As String

Set db = DAO.OpenDatabase("name.mdb")
strSQL = "0001-mtq-tblEmployees"
db.Execute strSQL
db.Close
Set db = Nothing

You're done.

--
Jim Carlock
http://www.microcosmotalk.com/
Post replies to the newsgroup.

Im working within an Access2000 database and writing code in VBA. What
i want to do is connect to an Access97 database through DAO and query
a table and import those records into my Access2000 database. The
tables have the same name and are similar in structure except they
have different names for fields. Ive been trying this mehtod but i
just cant seem to get it right:

Set db = ws.OpenDatabase(dbOld, True, True)

sSQL = "INSERT INTO tblGroup IN '" & CurrentDb.Name & "' " & _
"SELECT tblGroup.* FROM tblGroup.*;"

db.Execute sSQL

What am I doing wrong?

AGP
 
You're done, until you want to create a new table. Then
you'll have to learn how to loop through the tableDefs
and delete the tables you don't need. I think if you try to
call that twice you'll get an error on the second call, so
if it does error out, you'll want to set up a trap for the errata
in the code...

Private Sub CreateNewTable()
Dim strErr As String
Dim strSQL As String
Dim db As DAO.Database
On Error Goto LocalErr
...
etc.
...
Set db = Nothing
Exit Sub
LocalErr:
strErr = "ERROR " & CStr(Err.Number) & vbCrLf & _
Err.Description
MsgBox strError, vbCritical, "CreateNewTable"
End Sub

After you get that working, you can Loop through all
the tableDefs in the database and delete the tables that
are going to be rebuilt. After deleting the tables, I suggest
compacting the mdb as well, then rebuild the tables and
then compact again.

Something funny you'll notice, is that when you compact
an Access 2K mdb through code, it gets smaller than
when you've compacted it through Access.

--
Jim Carlock
http://www.microcosmotalk.com/
Post replies to the newsgroup.

In the Access 2K file, create a link to the Access 97 table
you'd like to import from. Name the link:

linkEmployees

Create a Query in the Access 2K file. Name the
sQlinkEmployees

Save and close the query. Make sure it's named
sQlinkEmployees.

Double - click on it to make sure the records retrieved are
correct.

Right click on it and open it in design mode. We're going to
change it to a MakeTable query now and rename it.

Right click inside the table area and click on the QueryType,
change it from Select to Make Table. After you do this, you
should get a prompt for a new table name.

When it asks for the new table name, name the table:
tblEmployees

After it is given a name, save the query itself under a new name.
Name it:
0001-mtq-tblEmployees

The reason I put 0001 on the front is because if it's the first
query in a set of queries, you'll be able to know what it's doing,
how many queries there are, etc.

After it's saved under the new, name, double-click on it.

It should ask for confirmation and ask if you are sure you'd like
to create the new table.

Once you click yes, you'll have an extremily fast table creation
mechanism. You can call the db.Execute methods on the query
name and your done.

This creates a fresh new table within Access every time.

Hope this helps.

Dim db As DAO.Database
Dim strSQL As String

Set db = DAO.OpenDatabase("name.mdb")
strSQL = "0001-mtq-tblEmployees"
db.Execute strSQL
db.Close
Set db = Nothing

You're done.

--
Jim Carlock
http://www.microcosmotalk.com/
Post replies to the newsgroup.

Im working within an Access2000 database and writing code in VBA. What
i want to do is connect to an Access97 database through DAO and query
a table and import those records into my Access2000 database. The
tables have the same name and are similar in structure except they
have different names for fields. Ive been trying this mehtod but i
just cant seem to get it right:

Set db = ws.OpenDatabase(dbOld, True, True)

sSQL = "INSERT INTO tblGroup IN '" & CurrentDb.Name & "' " & _
"SELECT tblGroup.* FROM tblGroup.*;"

db.Execute sSQL

What am I doing wrong?

AGP
 
DIOS said:
Well, I reviewed the SQL and it looks like so:

sSQL = "INSERT INTO tblEmployees (FullName, Initials) " & _
"SELECT EmpNme, Init " & _
"FROM tblEmployees " & _
"IN '" & sOldDBPath & "';"

CurrentDb.Execute sSQL

When I run this inside my Access VBA code I get the error:
Runtime error 3134
Syntax error in INSERT INTO statement.

There has to be a way of doing this. It seems like a simple query to
me but I just cant get the right syntax down to work properly.
Click to expand...

It doesn't look bad to me, provided of course all the table and field
names are correct, and provided path to the external database doesn't
contain a single-quote character. Do us a favor: set a break point at
CurrentDb.Execute sSQL
Click to expand...

and dump the value of sSQL in the Immediate Window, copy it, and paste
it into your next reply.
 
I would like to do that but I have many tables to import and id rather do it
all through code.
For me it is easier even though there is a workaround through liinked
tables. Plus i dont think
you can link an Access97 table inside an Access2000 database. i think i
tried that and it didnt work.

AGP
 
Ok here is the debug line for the sSQL line:
INSERT INTO tblEmployees (FullName, Initials) SELECT EmpNme, Init FROM
tblEmployees IN 'D:\Datafile\old.mdb';

Thanx
AGP
 
DIOS said:
Ok here is the debug line for the sSQL line:
INSERT INTO tblEmployees (FullName, Initials) SELECT EmpNme, Init FROM
tblEmployees IN 'D:\Datafile\old.mdb';
Click to expand...

I see nothing syntactically wrong with that statement, I'm afraid, and
one very like it works just fine for me. I suggest you verify that all
the field names are correct -- for example, that "EmpNme" shouldn't
really be "EmpName" -- that the table names are correct, and that the
path to the database is correct. Also, on the off chance that one of
these field names is the also in use as the name of some other global
object, try changing

... SELECT EmpNme, Init FROM
Click to expand...

to

... SELECT tblEmployees.[EmpNme], tblEmployees.[Init] FROM

and see if that makes any difference.
 
shot in the dark ....

"INSERT INTO tblEmployees (FullName, Initials) " & _
"(SELECT EmpNme, Init " & _
"FROM tblEmployees " & _
"IN '" & sOldDBPath & "')"

add braces around the result set
 
Back
Top