SQL insert data from another DB

[jimt]

I'm writing a module to refresh the data in all the tables in the current
database from the same named tables in a "master" database.

The table names to be refreshed are first written to a table in the current
database. The module reads the table via a do-loop. The next step executes a
SQL statement that deletes the records form the named table in the current
DB. The next step is to retrieve the records from the same named table in
another ("master") database and INSERT INTO the table in the current
database. The code currently reads:

apdStr = "INSERT INTO " & tbl_name & _
" SELECT * " & _
" FROM " & master_db & "; "
DoCmd.RunSQL apdStr

Example tbl_name="owner_name" (the same name in both current/receiving
database and in the master database)
master_db="C:\assessment\assessments.mdb"

What should the "From" statement read to get all the owner_name records from
the assessment.mdb and insert them into the owner_name table of the current
(child) database.

Thanks
Jim T.

 

[Douglas J. Steele]

apdStr = "INSERT INTO [" & tbl_name & "] " & _
" SELECT * " & _
" FROM [" & tbl_name & "] IN " master_db
CurrentDb.Execute apdStr, dbFailOnError

or

apdStr = "INSERT INTO [" & tbl_name & "] " & _
" SELECT * " & _
" FROM [;Database=" & master_db & "].[" & tbl_name & "]"
CurrentDb.Execute apdStr, dbFailOnError

 

[jimt]

Doug,

Thanks for the input. I tried the code you provide and received an error of
"Syntax error in the From clause". The print of the variable apdStr was:

INSERT INTO [abatements] SELECT * FROM [abatements] IN
C:\assessment\town.mdb

I've been through the VBA help and the code appear to be proper for the
Insert Into statement. Do you see something in error in the code?

Thanks
Jim T.

apdStr = "INSERT INTO [" & tbl_name & "] " & _
" SELECT * " & _
" FROM [" & tbl_name & "] IN " master_db
CurrentDb.Execute apdStr, dbFailOnError

or

apdStr = "INSERT INTO [" & tbl_name & "] " & _
" SELECT * " & _
" FROM [;Database=" & master_db & "].[" & tbl_name & "]"
CurrentDb.Execute apdStr, dbFailOnError


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm writing a module to refresh the data in all the tables in the current
database from the same named tables in a "master" database.

The table names to be refreshed are first written to a table in the
current
database. The module reads the table via a do-loop. The next step executes
a
SQL statement that deletes the records form the named table in the current
DB. The next step is to retrieve the records from the same named table in
another ("master") database and INSERT INTO the table in the current
database. The code currently reads:

apdStr = "INSERT INTO " & tbl_name & _
" SELECT * " & _
" FROM " & master_db & "; "
DoCmd.RunSQL apdStr

Example tbl_name="owner_name" (the same name in both current/receiving
database and in the master database)
master_db="C:\assessment\assessments.mdb"

What should the "From" statement read to get all the owner_name records
from
the assessment.mdb and insert them into the owner_name table of the
current
(child) database.

Thanks
Jim T.

 

[Douglas J. Steele]

Does it work if you use the other syntax I suggested?

INSERT INTO [abatements]
SELECT *
FROM [;Database=C:\assessment\town.mdb].[abatements]

You sure the user has the appropriate permissions on the C:\assessment
folder?


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,

Thanks for the input. I tried the code you provide and received an error
of
"Syntax error in the From clause". The print of the variable apdStr was:

INSERT INTO [abatements] SELECT * FROM [abatements] IN
C:\assessment\town.mdb

I've been through the VBA help and the code appear to be proper for the
Insert Into statement. Do you see something in error in the code?

Thanks
Jim T.

apdStr = "INSERT INTO [" & tbl_name & "] " & _
" SELECT * " & _
" FROM [" & tbl_name & "] IN " master_db
CurrentDb.Execute apdStr, dbFailOnError

or

apdStr = "INSERT INTO [" & tbl_name & "] " & _
" SELECT * " & _
" FROM [;Database=" & master_db & "].[" & tbl_name & "]"
CurrentDb.Execute apdStr, dbFailOnError


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm writing a module to refresh the data in all the tables in the
current
database from the same named tables in a "master" database.

The table names to be refreshed are first written to a table in the
current
database. The module reads the table via a do-loop. The next step
executes
a
SQL statement that deletes the records form the named table in the
current
DB. The next step is to retrieve the records from the same named table
in
another ("master") database and INSERT INTO the table in the current
database. The code currently reads:

apdStr = "INSERT INTO " & tbl_name & _
" SELECT * " & _
" FROM " & master_db & "; "
DoCmd.RunSQL apdStr

Example tbl_name="owner_name" (the same name in both current/receiving
database and in the master database)
master_db="C:\assessment\assessments.mdb"

What should the "From" statement read to get all the owner_name records
from
the assessment.mdb and insert them into the owner_name table of the
current
(child) database.

Thanks
Jim T.

 

[jimt]

Doug,

Again my thanks. I've been away from coding for awhile and with a crash of
my hard drive recently I've just got my Access/VB help somewhat working again.

Here's the result of two tests:
I sat up three variables; insert_stmt, select_stmt, and from_stmt, to print
in debug mode.
The variables match the text in the "apdStr" variable for each scenerio.

1. Your code directly

apdStr = "INSERT INTO [" & tbl_name & "] " & _
" SELECT * " & _
" FROM [" & tbl_name & "] IN " master_db
CurrentDb.Execute apdStr, dbFailOnError

Compile error:
Syntax error

2. Adding an ampersand in the From statement between "] IN " and the master_db
apdStr = "INSERT INTO [" & tbl_name & "] " & _
" SELECT * " & _
" FROM [" & tbl_name & "] IN " & master_db
CurrentDb.Execute apdStr, dbFailOnError

Run-time error '3131';
Systax error in From clause

insert_stmt: INSERT INTO [assessing]
select_stmt: Select *
from_stmt: From [assessing] IN C:\assessment\town.mdb
apdStr: INSERT INTO [assessing] SELECT * From [assessing] IN
C:\assessment\town.mdb

I've taken a look at the help on SQL/Insert Into topic. I have not been able
to fully understand the syntax defined in the help. I'll try a few more
syntax test but this is not an area I'm very familiar with (done some create
queries stmts) so any help is greatly appreciated.

Thanks
Jim T.

Does it work if you use the other syntax I suggested?

INSERT INTO [abatements]
SELECT *
FROM [;Database=C:\assessment\town.mdb].[abatements]

You sure the user has the appropriate permissions on the C:\assessment
folder?


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,

Thanks for the input. I tried the code you provide and received an error
of
"Syntax error in the From clause". The print of the variable apdStr was:

INSERT INTO [abatements] SELECT * FROM [abatements] IN
C:\assessment\town.mdb

I've been through the VBA help and the code appear to be proper for the
Insert Into statement. Do you see something in error in the code?

Thanks
Jim T.

apdStr = "INSERT INTO [" & tbl_name & "] " & _
" SELECT * " & _
" FROM [" & tbl_name & "] IN " master_db
CurrentDb.Execute apdStr, dbFailOnError

or

apdStr = "INSERT INTO [" & tbl_name & "] " & _
" SELECT * " & _
" FROM [;Database=" & master_db & "].[" & tbl_name & "]"
CurrentDb.Execute apdStr, dbFailOnError


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I'm writing a module to refresh the data in all the tables in the
current
database from the same named tables in a "master" database.

The table names to be refreshed are first written to a table in the
current
database. The module reads the table via a do-loop. The next step
executes
a
SQL statement that deletes the records form the named table in the
current
DB. The next step is to retrieve the records from the same named table
in
another ("master") database and INSERT INTO the table in the current
database. The code currently reads:

apdStr = "INSERT INTO " & tbl_name & _
" SELECT * " & _
" FROM " & master_db & "; "
DoCmd.RunSQL apdStr

Example tbl_name="owner_name" (the same name in both current/receiving
database and in the master database)
master_db="C:\assessment\assessments.mdb"

What should the "From" statement read to get all the owner_name records
from
the assessment.mdb and insert them into the owner_name table of the
current
(child) database.

Thanks
Jim T.

 

[Douglas J. Steele]

Sorry, I did inadvertently leave out the ampersand in the first case.

Is there a reason why you're refusing to try the other syntax? You also
haven't confirmed whether or not you're actually able to work with
C:\assessment\town.mdb

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,

Again my thanks. I've been away from coding for awhile and with a crash of
my hard drive recently I've just got my Access/VB help somewhat working
again.

Here's the result of two tests:
I sat up three variables; insert_stmt, select_stmt, and from_stmt, to
print
in debug mode.
The variables match the text in the "apdStr" variable for each scenerio.

1. Your code directly

apdStr = "INSERT INTO [" & tbl_name & "] " & _
" SELECT * " & _
" FROM [" & tbl_name & "] IN " master_db
CurrentDb.Execute apdStr, dbFailOnError

Compile error:
Syntax error

2. Adding an ampersand in the From statement between "] IN " and the
master_db
apdStr = "INSERT INTO [" & tbl_name & "] " & _
" SELECT * " & _
" FROM [" & tbl_name & "] IN " & master_db
CurrentDb.Execute apdStr, dbFailOnError

Run-time error '3131';
Systax error in From clause

insert_stmt: INSERT INTO [assessing]
select_stmt: Select *
from_stmt: From [assessing] IN C:\assessment\town.mdb
apdStr: INSERT INTO [assessing] SELECT * From [assessing] IN
C:\assessment\town.mdb

I've taken a look at the help on SQL/Insert Into topic. I have not been
able
to fully understand the syntax defined in the help. I'll try a few more
syntax test but this is not an area I'm very familiar with (done some
create
queries stmts) so any help is greatly appreciated.

Thanks
Jim T.

Does it work if you use the other syntax I suggested?

INSERT INTO [abatements]
SELECT *
FROM [;Database=C:\assessment\town.mdb].[abatements]

You sure the user has the appropriate permissions on the C:\assessment
folder?


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,

Thanks for the input. I tried the code you provide and received an
error
of
"Syntax error in the From clause". The print of the variable apdStr
was:

INSERT INTO [abatements] SELECT * FROM [abatements] IN
C:\assessment\town.mdb

I've been through the VBA help and the code appear to be proper for the
Insert Into statement. Do you see something in error in the code?

Thanks
Jim T.



:

apdStr = "INSERT INTO [" & tbl_name & "] " & _
" SELECT * " & _
" FROM [" & tbl_name & "] IN " master_db
CurrentDb.Execute apdStr, dbFailOnError

or

apdStr = "INSERT INTO [" & tbl_name & "] " & _
" SELECT * " & _
" FROM [;Database=" & master_db & "].[" & tbl_name & "]"
CurrentDb.Execute apdStr, dbFailOnError


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I'm writing a module to refresh the data in all the tables in the
current
database from the same named tables in a "master" database.

The table names to be refreshed are first written to a table in the
current
database. The module reads the table via a do-loop. The next step
executes
a
SQL statement that deletes the records form the named table in the
current
DB. The next step is to retrieve the records from the same named
table
in
another ("master") database and INSERT INTO the table in the current
database. The code currently reads:

apdStr = "INSERT INTO " & tbl_name & _
" SELECT * " & _
" FROM " & master_db & "; "
DoCmd.RunSQL apdStr

Example tbl_name="owner_name" (the same name in both
current/receiving
database and in the master database)
master_db="C:\assessment\assessments.mdb"

What should the "From" statement read to get all the owner_name
records
from
the assessment.mdb and insert them into the owner_name table of the
current
(child) database.

Thanks
Jim T.

 

[jimt]

Doug,

Thanks.
The town.mdb is the master DB and I've verified its existence in the
assessment directory as well as the first 10 tables in the DB.

The first code you provide looked like it followed the help syntax. I've
used the create query and RunSQL methods in the past so the syntax seemed to
be more familiar to me.

I've tried the second set of code and there are no errors. I ran the
procedure
through 15 loops of the Do loop and the code worked Great!

I'll gain some understanding of the syntax in your suggested code.

Thanks AGAIN
Jim T.

Sorry, I did inadvertently leave out the ampersand in the first case.

Is there a reason why you're refusing to try the other syntax? You also
haven't confirmed whether or not you're actually able to work with
C:\assessment\town.mdb

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,

Again my thanks. I've been away from coding for awhile and with a crash of
my hard drive recently I've just got my Access/VB help somewhat working
again.

Here's the result of two tests:
I sat up three variables; insert_stmt, select_stmt, and from_stmt, to
print
in debug mode.
The variables match the text in the "apdStr" variable for each scenerio.

1. Your code directly

apdStr = "INSERT INTO [" & tbl_name & "] " & _
" SELECT * " & _
" FROM [" & tbl_name & "] IN " master_db
CurrentDb.Execute apdStr, dbFailOnError

Compile error:
Syntax error

2. Adding an ampersand in the From statement between "] IN " and the
master_db
apdStr = "INSERT INTO [" & tbl_name & "] " & _
" SELECT * " & _
" FROM [" & tbl_name & "] IN " & master_db
CurrentDb.Execute apdStr, dbFailOnError

Run-time error '3131';
Systax error in From clause

insert_stmt: INSERT INTO [assessing]
select_stmt: Select *
from_stmt: From [assessing] IN C:\assessment\town.mdb
apdStr: INSERT INTO [assessing] SELECT * From [assessing] IN
C:\assessment\town.mdb

I've taken a look at the help on SQL/Insert Into topic. I have not been
able
to fully understand the syntax defined in the help. I'll try a few more
syntax test but this is not an area I'm very familiar with (done some
create
queries stmts) so any help is greatly appreciated.

Thanks
Jim T.

Does it work if you use the other syntax I suggested?

INSERT INTO [abatements]
SELECT *
FROM [;Database=C:\assessment\town.mdb].[abatements]

You sure the user has the appropriate permissions on the C:\assessment
folder?


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug,

Thanks for the input. I tried the code you provide and received an
error
of
"Syntax error in the From clause". The print of the variable apdStr
was:

INSERT INTO [abatements] SELECT * FROM [abatements] IN
C:\assessment\town.mdb

I've been through the VBA help and the code appear to be proper for the
Insert Into statement. Do you see something in error in the code?

Thanks
Jim T.



:

apdStr = "INSERT INTO [" & tbl_name & "] " & _
" SELECT * " & _
" FROM [" & tbl_name & "] IN " master_db
CurrentDb.Execute apdStr, dbFailOnError

or

apdStr = "INSERT INTO [" & tbl_name & "] " & _
" SELECT * " & _
" FROM [;Database=" & master_db & "].[" & tbl_name & "]"
CurrentDb.Execute apdStr, dbFailOnError


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I'm writing a module to refresh the data in all the tables in the
current
database from the same named tables in a "master" database.

The table names to be refreshed are first written to a table in the
current
database. The module reads the table via a do-loop. The next step
executes
a
SQL statement that deletes the records form the named table in the
current
DB. The next step is to retrieve the records from the same named
table
in
another ("master") database and INSERT INTO the table in the current
database. The code currently reads:

apdStr = "INSERT INTO " & tbl_name & _
" SELECT * " & _
" FROM " & master_db & "; "
DoCmd.RunSQL apdStr

Example tbl_name="owner_name" (the same name in both
current/receiving
database and in the master database)
master_db="C:\assessment\assessments.mdb"

What should the "From" statement read to get all the owner_name
records
from
the assessment.mdb and insert them into the owner_name table of the
current
(child) database.

Thanks
Jim T.