Import data from with the help of code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a TotalOT.mdb (currently opened), Table Name : "PostOTA", from a Form
Button Click Event I want to copy the data (datewise From ...To..)from the
another "MyOtB.Mdb",table "NewOt"(it is on the same PC, location : C:\Datas)
and add on my current opened Mdb.

Note : The both Table fields name are same and the Field sequence is also
same ( I mean NamePerson, Date, FromOt, EndOt, RemarksOT). There is no
primary key and Auto number field.

My need is to click a button on a Form and it should copy the data according
to given date (From..To..)and add it to the Table in the current opened
database.

Please advise me, what code should I write.


Regards.


Irshad.
 
You should be able use SQL like:

INSERT INTO PostOTA (NamePerson, Date, FromOt, EndOt, RemarksOT)
SELECT NamePerson, Date, FromOt, EndOt, RemarksOT
FROM NewOt IN C:\Datas\MyOTb.MDB

Hopefully you know how to run SQL using VBA. If not, post back.
 
Sir, Thank you very much for your advise. As I am not very good in VB, could
you please write me code, so that I can copy from here and use it.

Please also mention the line for Data sorting based on Date Field like From
date ..To Date.
The From date it can collect the Current opened Form : Form!PostDataForm!Text1
The End date can be collected from the Current opened Form :
Form!PostDataForm!Text2

If you will mention all , then I will be able to just copy and paste.

Regards.


Irshad
 
I'm using DAO below. If you're using Access 2000 or 2002, they don't have a
reference set to DAO by default, so you'll have to add it if you haven't
already done so.


Dim strSQL As String

strSQL = "INSERT INTO PostOTA (NamePerson, [Date], " & _
"FromOt, EndOt, RemarksOT) " & _
"SELECT NamePerson, [Date], FromOt, EndOt, " & _
"RemarksOT " & _
"FROM NewOt IN C:\Datas\MyOTb.MDB " & _
"WHERE [Date] BETWEEN " & _
Format(Form!PostDataForm!Text1, "\#mm\/dd\/yyyy\#") & _
" AND " & Format(Form!PostDataForm!Text2, "\#mm\/dd\/yyyy\#")

CurrentDb.Execute strSQL, dbFailOnError


Note that Date is not a good choice for a field in a table: it's a reserved
word, and using it for your own purposes can lead to problems. If you cannot
(or will not) change the name, make sure you enclose it in square brackets
as I did above.

Also note that date values must be delimited with # characters, and must be
in mm/dd/yyyy format, regardless of what short date format your machine is
set to in Regional Settings. (Okay, this isn't 100% true: you can use any
unambiguous format, such as yyyy-mm-dd or dd mmm yyyy. The point is, you
cannot use dd/mm/yyyy format.)
 
Sir,
Thanks for your code, I tried to use it, but failed, it shows error. The
below is the code which I have written (some names has changed, as the
previous field and table I wrote was an example and on code instead of Form
Wrote Forms as it was not recognizing):

Dim strSQL As String
strSQL = "INSERT INTO PostOTA (NamePerson, [Datees], " & _
"FromOt, EndOt, RemarksOT) " & _
"SELECT NamePerson, [Datees], FromOt, EndOt, " & _
"RemarksOT " & _
"FROM NewOt IN C:\TestInventory.mdb " & _
"WHERE [Datees] BETWEEN " & _
Format(Forms!Form1!Text1, "\#mm\/dd\/yyyy\#") & _
" AND " & Format(Forms!Form1!Text2, "\#mm\/dd\/yyyy\#")

CurrentDb.Execute strSQL, dbFailOnError

The error code is : 3131, and the message is : Syntax error in FROM clause

Please advise me.

Regards.

Irshad


Douglas J. Steele said:
I'm using DAO below. If you're using Access 2000 or 2002, they don't have a
reference set to DAO by default, so you'll have to add it if you haven't
already done so.


Dim strSQL As String

strSQL = "INSERT INTO PostOTA (NamePerson, [Date], " & _
"FromOt, EndOt, RemarksOT) " & _
"SELECT NamePerson, [Date], FromOt, EndOt, " & _
"RemarksOT " & _
"FROM NewOt IN C:\Datas\MyOTb.MDB " & _
"WHERE [Date] BETWEEN " & _
Format(Form!PostDataForm!Text1, "\#mm\/dd\/yyyy\#") & _
" AND " & Format(Form!PostDataForm!Text2, "\#mm\/dd\/yyyy\#")

CurrentDb.Execute strSQL, dbFailOnError


Note that Date is not a good choice for a field in a table: it's a reserved
word, and using it for your own purposes can lead to problems. If you cannot
(or will not) change the name, make sure you enclose it in square brackets
as I did above.

Also note that date values must be delimited with # characters, and must be
in mm/dd/yyyy format, regardless of what short date format your machine is
set to in Regional Settings. (Okay, this isn't 100% true: you can use any
unambiguous format, such as yyyy-mm-dd or dd mmm yyyy. The point is, you
cannot use dd/mm/yyyy format.)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Irshad Alam said:
Sir, Thank you very much for your advise. As I am not very good in VB,
could
you please write me code, so that I can copy from here and use it.

Please also mention the line for Data sorting based on Date Field like
From
date ..To Date.
The From date it can collect the Current opened Form :
Form!PostDataForm!Text1
The End date can be collected from the Current opened Form :
Form!PostDataForm!Text2

If you will mention all , then I will be able to just copy and paste.

Regards.


Irshad
Click to expand...
Click to expand...
 
Looks as though I could have been wrong about using the IN clause in the
FROM part: looking at the Help file again, it only seems to mention using
the IN clause for the table into which you're inserting the records.

You may have to create a linked table in your front-end that points to the
correct external table and use it instead.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Irshad Alam said:
Sir,
Thanks for your code, I tried to use it, but failed, it shows error. The
below is the code which I have written (some names has changed, as the
previous field and table I wrote was an example and on code instead of
Form
Wrote Forms as it was not recognizing):

Dim strSQL As String
strSQL = "INSERT INTO PostOTA (NamePerson, [Datees], " & _
"FromOt, EndOt, RemarksOT) " & _
"SELECT NamePerson, [Datees], FromOt, EndOt, " & _
"RemarksOT " & _
"FROM NewOt IN C:\TestInventory.mdb " & _
"WHERE [Datees] BETWEEN " & _
Format(Forms!Form1!Text1, "\#mm\/dd\/yyyy\#") & _
" AND " & Format(Forms!Form1!Text2, "\#mm\/dd\/yyyy\#")

CurrentDb.Execute strSQL, dbFailOnError

The error code is : 3131, and the message is : Syntax error in FROM clause

Please advise me.

Regards.

Irshad


Douglas J. Steele said:
I'm using DAO below. If you're using Access 2000 or 2002, they don't have
a
reference set to DAO by default, so you'll have to add it if you haven't
already done so.


Dim strSQL As String

strSQL = "INSERT INTO PostOTA (NamePerson, [Date], " & _
"FromOt, EndOt, RemarksOT) " & _
"SELECT NamePerson, [Date], FromOt, EndOt, " & _
"RemarksOT " & _
"FROM NewOt IN C:\Datas\MyOTb.MDB " & _
"WHERE [Date] BETWEEN " & _
Format(Form!PostDataForm!Text1, "\#mm\/dd\/yyyy\#") & _
" AND " & Format(Form!PostDataForm!Text2, "\#mm\/dd\/yyyy\#")

CurrentDb.Execute strSQL, dbFailOnError


Note that Date is not a good choice for a field in a table: it's a
reserved
word, and using it for your own purposes can lead to problems. If you
cannot
(or will not) change the name, make sure you enclose it in square
brackets
as I did above.

Also note that date values must be delimited with # characters, and must
be
in mm/dd/yyyy format, regardless of what short date format your machine
is
set to in Regional Settings. (Okay, this isn't 100% true: you can use any
unambiguous format, such as yyyy-mm-dd or dd mmm yyyy. The point is, you
cannot use dd/mm/yyyy format.)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Irshad Alam said:
Sir, Thank you very much for your advise. As I am not very good in VB,
could
you please write me code, so that I can copy from here and use it.

Please also mention the line for Data sorting based on Date Field like
From
date ..To Date.
The From date it can collect the Current opened Form :
Form!PostDataForm!Text1
The End date can be collected from the Current opened Form :
Form!PostDataForm!Text2

If you will mention all , then I will be able to just copy and paste.

Regards.


Irshad



:

You should be able use SQL like:

INSERT INTO PostOTA (NamePerson, Date, FromOt, EndOt, RemarksOT)
SELECT NamePerson, Date, FromOt, EndOt, RemarksOT
FROM NewOt IN C:\Datas\MyOTb.MDB

Hopefully you know how to run SQL using VBA. If not, post back.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a TotalOT.mdb (currently opened), Table Name : "PostOTA",
from a
Form
Button Click Event I want to copy the data (datewise From
...To..)from
the
another "MyOtB.Mdb",table "NewOt"(it is on the same PC, location :
C:\Datas)
and add on my current opened Mdb.

Note : The both Table fields name are same and the Field sequence is
also
same ( I mean NamePerson, Date, FromOt, EndOt, RemarksOT). There is
no
primary key and Auto number field.

My need is to click a button on a Form and it should copy the data
according
to given date (From..To..)and add it to the Table in the current
opened
database.

Please advise me, what code should I write.


Regards.


Irshad.
Click to expand...
Click to expand...
Click to expand...
 
I tried and traced the mistake, from the help files. It was that the mdb
location should be closed in 'MdbFile Location'. I changed and it worked
perfect.

Thanks for your valuable advises.

Regards.

Irshad.



Douglas J. Steele said:
Looks as though I could have been wrong about using the IN clause in the
FROM part: looking at the Help file again, it only seems to mention using
the IN clause for the table into which you're inserting the records.

You may have to create a linked table in your front-end that points to the
correct external table and use it instead.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Irshad Alam said:
Sir,
Thanks for your code, I tried to use it, but failed, it shows error. The
below is the code which I have written (some names has changed, as the
previous field and table I wrote was an example and on code instead of
Form
Wrote Forms as it was not recognizing):

Dim strSQL As String
strSQL = "INSERT INTO PostOTA (NamePerson, [Datees], " & _
"FromOt, EndOt, RemarksOT) " & _
"SELECT NamePerson, [Datees], FromOt, EndOt, " & _
"RemarksOT " & _
"FROM NewOt IN C:\TestInventory.mdb " & _
"WHERE [Datees] BETWEEN " & _
Format(Forms!Form1!Text1, "\#mm\/dd\/yyyy\#") & _
" AND " & Format(Forms!Form1!Text2, "\#mm\/dd\/yyyy\#")

CurrentDb.Execute strSQL, dbFailOnError

The error code is : 3131, and the message is : Syntax error in FROM clause

Please advise me.

Regards.

Irshad


Douglas J. Steele said:
I'm using DAO below. If you're using Access 2000 or 2002, they don't have
a
reference set to DAO by default, so you'll have to add it if you haven't
already done so.


Dim strSQL As String

strSQL = "INSERT INTO PostOTA (NamePerson, [Date], " & _
"FromOt, EndOt, RemarksOT) " & _
"SELECT NamePerson, [Date], FromOt, EndOt, " & _
"RemarksOT " & _
"FROM NewOt IN C:\Datas\MyOTb.MDB " & _
"WHERE [Date] BETWEEN " & _
Format(Form!PostDataForm!Text1, "\#mm\/dd\/yyyy\#") & _
" AND " & Format(Form!PostDataForm!Text2, "\#mm\/dd\/yyyy\#")

CurrentDb.Execute strSQL, dbFailOnError


Note that Date is not a good choice for a field in a table: it's a
reserved
word, and using it for your own purposes can lead to problems. If you
cannot
(or will not) change the name, make sure you enclose it in square
brackets
as I did above.

Also note that date values must be delimited with # characters, and must
be
in mm/dd/yyyy format, regardless of what short date format your machine
is
set to in Regional Settings. (Okay, this isn't 100% true: you can use any
unambiguous format, such as yyyy-mm-dd or dd mmm yyyy. The point is, you
cannot use dd/mm/yyyy format.)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Sir, Thank you very much for your advise. As I am not very good in VB,
could
you please write me code, so that I can copy from here and use it.

Please also mention the line for Data sorting based on Date Field like
From
date ..To Date.
The From date it can collect the Current opened Form :
Form!PostDataForm!Text1
The End date can be collected from the Current opened Form :
Form!PostDataForm!Text2

If you will mention all , then I will be able to just copy and paste.

Regards.


Irshad



:

You should be able use SQL like:

INSERT INTO PostOTA (NamePerson, Date, FromOt, EndOt, RemarksOT)
SELECT NamePerson, Date, FromOt, EndOt, RemarksOT
FROM NewOt IN C:\Datas\MyOTb.MDB

Hopefully you know how to run SQL using VBA. If not, post back.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a TotalOT.mdb (currently opened), Table Name : "PostOTA",
from a
Form
Button Click Event I want to copy the data (datewise From
...To..)from
the
another "MyOtB.Mdb",table "NewOt"(it is on the same PC, location :
C:\Datas)
and add on my current opened Mdb.

Note : The both Table fields name are same and the Field sequence is
also
same ( I mean NamePerson, Date, FromOt, EndOt, RemarksOT). There is
no
primary key and Auto number field.

My need is to click a button on a Form and it should copy the data
according
to given date (From..To..)and add it to the Table in the current
opened
database.

Please advise me, what code should I write.


Regards.


Irshad.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top