INSERT INTO another Database

[Ben]

I have a basic Insert Into code and it works for inserting the code into
another table. What would the code look like after VALUES to insert the
values into a table in another database?
Thanks

Ben

 

[Douglas J. Steele]

It would be simpler to see what code you've currently got and tell you how
to change it than to give you "blue sky" code.

When you post your code, tell us the full path to the target database.

 

[fredg]

I have a basic Insert Into code and it works for inserting the code into
another table. What would the code look like after VALUES to insert the
values into a table in another database?
Thanks

Ben

Assuming you wish to enter a new last name "Jones" into the new
record:

"INSERT INTO [YourDbName] ( [LastName] ) IN
'C:\PathToDb\YourDbName.mdb' Select 'Jones' as Exp;"

 

[Douglas J. Steele]

You sure about that Fred?

I'd think

INSERT INTO [YourTableName] ( [LastName] ) IN
'C:\PathToDb\YourDbName.mdb'
Values( 'Jones')

or, my preference

INSERT INTO [Database=C:\PathToDb\YourDbName.mdb].[YourTableName] (
[LastName] )
Values( 'Jones')


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a basic Insert Into code and it works for inserting the code into
another table. What would the code look like after VALUES to insert the
values into a table in another database?
Thanks

Ben

Assuming you wish to enter a new last name "Jones" into the new
record:

"INSERT INTO [YourDbName] ( [LastName] ) IN
'C:\PathToDb\YourDbName.mdb' Select 'Jones' as Exp;"

 

[Douglas J. Steele]

Or, to correct my error,

INSERT INTO [;Database=C:\PathToDb\YourDbName.mdb].[YourTableName] (
[LastName] )
Values( 'Jones')


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

You sure about that Fred?

I'd think

INSERT INTO [YourTableName] ( [LastName] ) IN
'C:\PathToDb\YourDbName.mdb'
Values( 'Jones')

or, my preference

INSERT INTO [Database=C:\PathToDb\YourDbName.mdb].[YourTableName] (
[LastName] )
Values( 'Jones')


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a basic Insert Into code and it works for inserting the code into
another table. What would the code look like after VALUES to insert the
values into a table in another database?
Thanks

Ben

Assuming you wish to enter a new last name "Jones" into the new
record:

"INSERT INTO [YourDbName] ( [LastName] ) IN
'C:\PathToDb\YourDbName.mdb' Select 'Jones' as Exp;"

 

[fredg]

You sure about that Fred?

I'd think

INSERT INTO [YourTableName] ( [LastName] ) IN
'C:\PathToDb\YourDbName.mdb'
Values( 'Jones')

or, my preference

INSERT INTO [Database=C:\PathToDb\YourDbName.mdb].[YourTableName] (
[LastName] )
Values( 'Jones')

Hi Doug,
Copied exactly from a Click event on my computer:

CurrentDb.Execute "Insert INTO [Household Addresses] ([LastName]) IN
'C:\My Household\HouseAddress.mdb' Select 'Jones' as Exp;"

Also

CurrentDb.Execute "Insert INTO [Household Addresses] ([LastName]) IN
'C:\My Household\HouseAddress.mdb' Values ('Tested');"

Both worked.

 

[Douglas J. Steele]

You sure about that Fred?

I'd think

INSERT INTO [YourTableName] ( [LastName] ) IN
'C:\PathToDb\YourDbName.mdb'
Values( 'Jones')

or, my preference

INSERT INTO [Database=C:\PathToDb\YourDbName.mdb].[YourTableName] (
[LastName] )
Values( 'Jones')

Hi Doug,
Copied exactly from a Click event on my computer:

CurrentDb.Execute "Insert INTO [Household Addresses] ([LastName]) IN
'C:\My Household\HouseAddress.mdb' Select 'Jones' as Exp;"

Also

CurrentDb.Execute "Insert INTO [Household Addresses] ([LastName]) IN
'C:\My Household\HouseAddress.mdb' Values ('Tested');"

Sorry that I wasn't clearer, Fred.

Your response said

"INSERT INTO [YourDbName] ( [LastName] ) IN
'C:\PathToDb\YourDbName.mdb' Select 'Jones' as Exp;"

The "INTO [Your DbName]" was my point: that should have been "INTO [Your
TableName]"

The

CurrentDb.Execute "Insert INTO [Household Addresses] ([LastName]) IN
'C:\My Household\HouseAddress.mdb' Select 'Jones' as Exp;"

does surprise me, though. What version of Access were you using? I don't
that will work in some of the "older versions"

 

[fredg]

You sure about that Fred?

I'd think

INSERT INTO [YourTableName] ( [LastName] ) IN
'C:\PathToDb\YourDbName.mdb'
Values( 'Jones')

or, my preference

INSERT INTO [Database=C:\PathToDb\YourDbName.mdb].[YourTableName] (
[LastName] )
Values( 'Jones')

Hi Doug,
Copied exactly from a Click event on my computer:

CurrentDb.Execute "Insert INTO [Household Addresses] ([LastName]) IN
'C:\My Household\HouseAddress.mdb' Select 'Jones' as Exp;"

Also

CurrentDb.Execute "Insert INTO [Household Addresses] ([LastName]) IN
'C:\My Household\HouseAddress.mdb' Values ('Tested');"

Sorry that I wasn't clearer, Fred.

Your response said

"INSERT INTO [YourDbName] ( [LastName] ) IN
'C:\PathToDb\YourDbName.mdb' Select 'Jones' as Exp;"

The "INTO [Your DbName]" was my point: that should have been "INTO [Your
TableName]"

Yes you are correct regarding the table name.
I confused myself by having a similar table name "Household Addresses"
and database name "HouseAddress".
I think this Db was the first I ever made, many, many eon's ago.

The

CurrentDb.Execute "Insert INTO [Household Addresses] ([LastName]) IN
'C:\My Household\HouseAddress.mdb' Select 'Jones' as Exp;"

does surprise me, though. What version of Access were you using? I don't
that will work in some of the "older versions"

I'm using Access 2002.