trying to copy a photo from one db to another

  • Thread starter Thread starter Emma
  • Start date Start date
E

Emma

Hi I know this isn't the forum for coding but I'm at a standstill. How do I
copy a photo from one database and put it in another. I've been reading about
getchunks but I'm not sure that's what I need to do. I'm using appendchunk to
store it in the second database and that seems to be working fine. It's just
how do I get the photo out of the first database. Here is my convoluted code,
any help would be appreciated:

'Read in the record number to be inserted
RecordNo3 = CLng(Request.QueryString("ID"))

'Create an ADO connection object
Set Conn3 = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less
connection
Conn3.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("\db\FileDB.mdb")

'Create an ADO recordset object
Set oRS3 = Server.CreateObject("ADODB.Recordset")
'set objField=Server.CreateObject("ADODB.field")

'Initialise the strSQL variable with an SQL statement to query the database
sSQL3 = "SELECT Files.* FROM Files WHERE ID=" & RecordNo3

Set oRS3 = Conn3.Execute(sSQL3)

fileName = oRS3("File Name")
fileSize = oRS3("File Size")
'fileData = oRS3(fileName).GetChunk(fileSize)
'fileData = "/AHousingHelpCentre/LoadPhoto.asp?ID=oRS("ID").Value"
'fileData = objField.GetChunk(size)
contentType = oRS3("Content Type")
 
I have never seen a statement like this before with an INSERT and SELECT
together. I've been trying it using:

sSQL3 = INSERT * INTO '\db\FileDB_bak.mdb' VALUES SELECT Files.* FROM Files
WHERE ID=" & RecordNo3

Set oRS3 = Conn3.Execute(sSQL3)

Can you offer me some advice I'm lost

To insert the whole row?

Alex Dybenko said:
Hi,
have you tried to use append or update query? Much easier!

Insert into Files IN 'c:\mymdb' ([File Name]) Select [File Name] from Files
Where ID=1

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


Emma said:
Hi I know this isn't the forum for coding but I'm at a standstill. How do
I
copy a photo from one database and put it in another. I've been reading
about
getchunks but I'm not sure that's what I need to do. I'm using appendchunk
to
store it in the second database and that seems to be working fine. It's
just
how do I get the photo out of the first database. Here is my convoluted
code,
any help would be appreciated:

'Read in the record number to be inserted
RecordNo3 = CLng(Request.QueryString("ID"))

'Create an ADO connection object
Set Conn3 = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less
connection
Conn3.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("\db\FileDB.mdb")

'Create an ADO recordset object
Set oRS3 = Server.CreateObject("ADODB.Recordset")
'set objField=Server.CreateObject("ADODB.field")

'Initialise the strSQL variable with an SQL statement to query the
database
sSQL3 = "SELECT Files.* FROM Files WHERE ID=" & RecordNo3

Set oRS3 = Conn3.Execute(sSQL3)

fileName = oRS3("File Name")
fileSize = oRS3("File Size")
'fileData = oRS3(fileName).GetChunk(fileSize)
'fileData = "/AHousingHelpCentre/LoadPhoto.asp?ID=oRS("ID").Value"
'fileData = objField.GetChunk(size)
contentType = oRS3("Content Type")
Click to expand...
Click to expand...
 
Ok I've altered the statement a little bit trying to copy the whole row
however I keep getting an error message and it points to the word INTO here
it is:

sSQL3 = INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb'(*) SELECT
Files.* FROM Files WHERE ID=" & RecordNo3

Set oRS3 = Conn3.Execute(sSQL3)

Emma said:
I have never seen a statement like this before with an INSERT and SELECT
together. I've been trying it using:

sSQL3 = INSERT * INTO '\db\FileDB_bak.mdb' VALUES SELECT Files.* FROM Files
WHERE ID=" & RecordNo3

Set oRS3 = Conn3.Execute(sSQL3)

Can you offer me some advice I'm lost

To insert the whole row?

Alex Dybenko said:
Hi,
have you tried to use append or update query? Much easier!

Insert into Files IN 'c:\mymdb' ([File Name]) Select [File Name] from Files
Where ID=1

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


Emma said:
Hi I know this isn't the forum for coding but I'm at a standstill. How do
I
copy a photo from one database and put it in another. I've been reading
about
getchunks but I'm not sure that's what I need to do. I'm using appendchunk
to
store it in the second database and that seems to be working fine. It's
just
how do I get the photo out of the first database. Here is my convoluted
code,
any help would be appreciated:

'Read in the record number to be inserted
RecordNo3 = CLng(Request.QueryString("ID"))

'Create an ADO connection object
Set Conn3 = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less
connection
Conn3.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("\db\FileDB.mdb")

'Create an ADO recordset object
Set oRS3 = Server.CreateObject("ADODB.Recordset")
'set objField=Server.CreateObject("ADODB.field")

'Initialise the strSQL variable with an SQL statement to query the
database
sSQL3 = "SELECT Files.* FROM Files WHERE ID=" & RecordNo3

Set oRS3 = Conn3.Execute(sSQL3)

fileName = oRS3("File Name")
fileSize = oRS3("File Size")
'fileData = oRS3(fileName).GetChunk(fileSize)
'fileData = "/AHousingHelpCentre/LoadPhoto.asp?ID=oRS("ID").Value"
'fileData = objField.GetChunk(size)
contentType = oRS3("Content Type")
Click to expand...
Click to expand...
Click to expand...
 
The (*) is incorrect: it either needs to be a list of field names, or left
out.

sSQL3 = INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb' " & _
"SELECT Files.* FROM Files WHERE ID=" & RecordNo3

although I would recommend including the field names:

sSQL3 = INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb' " & _
"(FIeld1, Field2, Field3) " & _
"SELECT Field1, Field2, Field3 FROM Files WHERE ID=" & RecordNo3

Another alternative is

sSQL3 = INSERT INTO [;Database=\EmilyLeeParsons\db\FileDB_bak.mdb].Files " &
_
"(FIeld1, Field2, Field3) " & _
"SELECT Field1, Field2, Field3 FROM Files WHERE ID=" & RecordNo3



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Emma said:
Ok I've altered the statement a little bit trying to copy the whole row
however I keep getting an error message and it points to the word INTO
here
it is:

sSQL3 = INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb'(*)
SELECT
Files.* FROM Files WHERE ID=" & RecordNo3

Set oRS3 = Conn3.Execute(sSQL3)

Emma said:
I have never seen a statement like this before with an INSERT and SELECT
together. I've been trying it using:

sSQL3 = INSERT * INTO '\db\FileDB_bak.mdb' VALUES SELECT Files.* FROM
Files
WHERE ID=" & RecordNo3

Set oRS3 = Conn3.Execute(sSQL3)

Can you offer me some advice I'm lost

To insert the whole row?

Alex Dybenko said:
Hi,
have you tried to use append or update query? Much easier!

Insert into Files IN 'c:\mymdb' ([File Name]) Select [File Name] from
Files
Where ID=1

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


Hi I know this isn't the forum for coding but I'm at a standstill.
How do
I
copy a photo from one database and put it in another. I've been
reading
about
getchunks but I'm not sure that's what I need to do. I'm using
appendchunk
to
store it in the second database and that seems to be working fine.
It's
just
how do I get the photo out of the first database. Here is my
convoluted
code,
any help would be appreciated:

'Read in the record number to be inserted
RecordNo3 = CLng(Request.QueryString("ID"))

'Create an ADO connection object
Set Conn3 = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less
connection
Conn3.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("\db\FileDB.mdb")

'Create an ADO recordset object
Set oRS3 = Server.CreateObject("ADODB.Recordset")
'set objField=Server.CreateObject("ADODB.field")

'Initialise the strSQL variable with an SQL statement to query the
database
sSQL3 = "SELECT Files.* FROM Files WHERE ID=" & RecordNo3

Set oRS3 = Conn3.Execute(sSQL3)

fileName = oRS3("File Name")
fileSize = oRS3("File Size")
'fileData = oRS3(fileName).GetChunk(fileSize)
'fileData =
"/AHousingHelpCentre/LoadPhoto.asp?ID=oRS("ID").Value"
'fileData = objField.GetChunk(size)
contentType = oRS3("Content Type")
Click to expand...
Click to expand...
Click to expand...
 
Hi everything I do stops on the INTO word here's more of my code maybe this
will help?

<% 'Dimension variables
Dim Conn3, Conn4 'Holds the Database Connection Object
Dim oRS3 'Holds the recordset for the record to be inserted
Dim sSQL3 'Holds the SQL query to query the database
Dim RecordNo3 'Holds the record number to be inserted

'Read in the record number to be inserted
RecordNo3 = CLng(Request.QueryString("ID"))

'Create an ADO connection object
Set Conn3 = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less
connection
Conn3.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("\EmilyLeeParsons\db\FileDB.mdb")

sSQL3 = INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb' " & _
"([File Name], [File Size], [File Data]) " & _
"SELECT [File Name], [File Size], [File Data] FROM Files WHERE ID=" &
RecordNo3


Conn3.Execute(sSQL3)

'Reset server objects
oRS3.Close
Set oRS3 = Nothing
Set Conn3 = Nothing
oRS4.Close
Set oRS4 = Nothing
Set Conn4 = Nothing




Douglas J. Steele said:
The (*) is incorrect: it either needs to be a list of field names, or left
out.

sSQL3 = INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb' " & _
"SELECT Files.* FROM Files WHERE ID=" & RecordNo3

although I would recommend including the field names:

sSQL3 = INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb' " & _
"(FIeld1, Field2, Field3) " & _
"SELECT Field1, Field2, Field3 FROM Files WHERE ID=" & RecordNo3

Another alternative is

sSQL3 = INSERT INTO [;Database=\EmilyLeeParsons\db\FileDB_bak.mdb].Files " &
_
"(FIeld1, Field2, Field3) " & _
"SELECT Field1, Field2, Field3 FROM Files WHERE ID=" & RecordNo3



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Emma said:
Ok I've altered the statement a little bit trying to copy the whole row
however I keep getting an error message and it points to the word INTO
here
it is:

sSQL3 = INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb'(*)
SELECT
Files.* FROM Files WHERE ID=" & RecordNo3

Set oRS3 = Conn3.Execute(sSQL3)

Emma said:
I have never seen a statement like this before with an INSERT and SELECT
together. I've been trying it using:

sSQL3 = INSERT * INTO '\db\FileDB_bak.mdb' VALUES SELECT Files.* FROM
Files
WHERE ID=" & RecordNo3

Set oRS3 = Conn3.Execute(sSQL3)

Can you offer me some advice I'm lost

To insert the whole row?

:

Hi,
have you tried to use append or update query? Much easier!

Insert into Files IN 'c:\mymdb' ([File Name]) Select [File Name] from
Files
Where ID=1

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


Hi I know this isn't the forum for coding but I'm at a standstill.
How do
I
copy a photo from one database and put it in another. I've been
reading
about
getchunks but I'm not sure that's what I need to do. I'm using
appendchunk
to
store it in the second database and that seems to be working fine.
It's
just
how do I get the photo out of the first database. Here is my
convoluted
code,
any help would be appreciated:

'Read in the record number to be inserted
RecordNo3 = CLng(Request.QueryString("ID"))

'Create an ADO connection object
Set Conn3 = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less
connection
Conn3.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("\db\FileDB.mdb")

'Create an ADO recordset object
Set oRS3 = Server.CreateObject("ADODB.Recordset")
'set objField=Server.CreateObject("ADODB.field")

'Initialise the strSQL variable with an SQL statement to query the
database
sSQL3 = "SELECT Files.* FROM Files WHERE ID=" & RecordNo3

Set oRS3 = Conn3.Execute(sSQL3)

fileName = oRS3("File Name")
fileSize = oRS3("File Size")
'fileData = oRS3(fileName).GetChunk(fileSize)
'fileData =
"/AHousingHelpCentre/LoadPhoto.asp?ID=oRS("ID").Value"
'fileData = objField.GetChunk(size)
contentType = oRS3("Content Type")
Click to expand...
Click to expand...
Click to expand...
 
I missed the fact that the opening quote is missing!

sSQL3 = "INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb' " & _
"([File Name], [File Size], [File Data]) " & _
"SELECT [File Name], [File Size], [File Data] FROM Files WHERE ID=" &
RecordNo3



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Emma said:
Hi everything I do stops on the INTO word here's more of my code maybe
this
will help?

<% 'Dimension variables
Dim Conn3, Conn4 'Holds the Database Connection Object
Dim oRS3 'Holds the recordset for the record to be inserted
Dim sSQL3 'Holds the SQL query to query the database
Dim RecordNo3 'Holds the record number to be inserted

'Read in the record number to be inserted
RecordNo3 = CLng(Request.QueryString("ID"))

'Create an ADO connection object
Set Conn3 = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less
connection
Conn3.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("\EmilyLeeParsons\db\FileDB.mdb")

sSQL3 = INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb' " & _
"([File Name], [File Size], [File Data]) " & _
"SELECT [File Name], [File Size], [File Data] FROM Files WHERE ID=" &
RecordNo3


Conn3.Execute(sSQL3)

'Reset server objects
oRS3.Close
Set oRS3 = Nothing
Set Conn3 = Nothing
oRS4.Close
Set oRS4 = Nothing
Set Conn4 = Nothing




Douglas J. Steele said:
The (*) is incorrect: it either needs to be a list of field names, or
left
out.

sSQL3 = INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb' " & _
"SELECT Files.* FROM Files WHERE ID=" & RecordNo3

although I would recommend including the field names:

sSQL3 = INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb' " & _
"(FIeld1, Field2, Field3) " & _
"SELECT Field1, Field2, Field3 FROM Files WHERE ID=" & RecordNo3

Another alternative is

sSQL3 = INSERT INTO [;Database=\EmilyLeeParsons\db\FileDB_bak.mdb].Files
" &
_
"(FIeld1, Field2, Field3) " & _
"SELECT Field1, Field2, Field3 FROM Files WHERE ID=" & RecordNo3



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Emma said:
Ok I've altered the statement a little bit trying to copy the whole row
however I keep getting an error message and it points to the word INTO
here
it is:

sSQL3 = INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb'(*)
SELECT
Files.* FROM Files WHERE ID=" & RecordNo3

Set oRS3 = Conn3.Execute(sSQL3)

:

I have never seen a statement like this before with an INSERT and
SELECT
together. I've been trying it using:

sSQL3 = INSERT * INTO '\db\FileDB_bak.mdb' VALUES SELECT Files.* FROM
Files
WHERE ID=" & RecordNo3

Set oRS3 = Conn3.Execute(sSQL3)

Can you offer me some advice I'm lost

To insert the whole row?

:

Hi,
have you tried to use append or update query? Much easier!

Insert into Files IN 'c:\mymdb' ([File Name]) Select [File Name]
from
Files
Where ID=1

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


Hi I know this isn't the forum for coding but I'm at a standstill.
How do
I
copy a photo from one database and put it in another. I've been
reading
about
getchunks but I'm not sure that's what I need to do. I'm using
appendchunk
to
store it in the second database and that seems to be working fine.
It's
just
how do I get the photo out of the first database. Here is my
convoluted
code,
any help would be appreciated:

'Read in the record number to be inserted
RecordNo3 = CLng(Request.QueryString("ID"))

'Create an ADO connection object
Set Conn3 = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a
DSN-less
connection
Conn3.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("\db\FileDB.mdb")

'Create an ADO recordset object
Set oRS3 = Server.CreateObject("ADODB.Recordset")
'set objField=Server.CreateObject("ADODB.field")

'Initialise the strSQL variable with an SQL statement to query the
database
sSQL3 = "SELECT Files.* FROM Files WHERE ID=" & RecordNo3

Set oRS3 = Conn3.Execute(sSQL3)

fileName = oRS3("File Name")
fileSize = oRS3("File Size")
'fileData = oRS3(fileName).GetChunk(fileSize)
'fileData =
"/AHousingHelpCentre/LoadPhoto.asp?ID=oRS("ID").Value"
'fileData = objField.GetChunk(size)
contentType = oRS3("Content Type")
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the quick response now I'm getting a type mismatch error, please
note that there are photos in the DBs

Douglas J. Steele said:
I missed the fact that the opening quote is missing!

sSQL3 = "INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb' " & _
"([File Name], [File Size], [File Data]) " & _
"SELECT [File Name], [File Size], [File Data] FROM Files WHERE ID=" &
RecordNo3



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Emma said:
Hi everything I do stops on the INTO word here's more of my code maybe
this
will help?

<% 'Dimension variables
Dim Conn3, Conn4 'Holds the Database Connection Object
Dim oRS3 'Holds the recordset for the record to be inserted
Dim sSQL3 'Holds the SQL query to query the database
Dim RecordNo3 'Holds the record number to be inserted

'Read in the record number to be inserted
RecordNo3 = CLng(Request.QueryString("ID"))

'Create an ADO connection object
Set Conn3 = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less
connection
Conn3.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("\EmilyLeeParsons\db\FileDB.mdb")

sSQL3 = INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb' " & _
"([File Name], [File Size], [File Data]) " & _
"SELECT [File Name], [File Size], [File Data] FROM Files WHERE ID=" &
RecordNo3


Conn3.Execute(sSQL3)

'Reset server objects
oRS3.Close
Set oRS3 = Nothing
Set Conn3 = Nothing
oRS4.Close
Set oRS4 = Nothing
Set Conn4 = Nothing




Douglas J. Steele said:
The (*) is incorrect: it either needs to be a list of field names, or
left
out.

sSQL3 = INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb' " & _
"SELECT Files.* FROM Files WHERE ID=" & RecordNo3

although I would recommend including the field names:

sSQL3 = INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb' " & _
"(FIeld1, Field2, Field3) " & _
"SELECT Field1, Field2, Field3 FROM Files WHERE ID=" & RecordNo3

Another alternative is

sSQL3 = INSERT INTO [;Database=\EmilyLeeParsons\db\FileDB_bak.mdb].Files
" &
_
"(FIeld1, Field2, Field3) " & _
"SELECT Field1, Field2, Field3 FROM Files WHERE ID=" & RecordNo3



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ok I've altered the statement a little bit trying to copy the whole row
however I keep getting an error message and it points to the word INTO
here
it is:

sSQL3 = INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb'(*)
SELECT
Files.* FROM Files WHERE ID=" & RecordNo3

Set oRS3 = Conn3.Execute(sSQL3)

:

I have never seen a statement like this before with an INSERT and
SELECT
together. I've been trying it using:

sSQL3 = INSERT * INTO '\db\FileDB_bak.mdb' VALUES SELECT Files.* FROM
Files
WHERE ID=" & RecordNo3

Set oRS3 = Conn3.Execute(sSQL3)

Can you offer me some advice I'm lost

To insert the whole row?

:

Hi,
have you tried to use append or update query? Much easier!

Insert into Files IN 'c:\mymdb' ([File Name]) Select [File Name]
from
Files
Where ID=1

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


Hi I know this isn't the forum for coding but I'm at a standstill.
How do
I
copy a photo from one database and put it in another. I've been
reading
about
getchunks but I'm not sure that's what I need to do. I'm using
appendchunk
to
store it in the second database and that seems to be working fine.
It's
just
how do I get the photo out of the first database. Here is my
convoluted
code,
any help would be appreciated:

'Read in the record number to be inserted
RecordNo3 = CLng(Request.QueryString("ID"))

'Create an ADO connection object
Set Conn3 = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a
DSN-less
connection
Conn3.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("\db\FileDB.mdb")

'Create an ADO recordset object
Set oRS3 = Server.CreateObject("ADODB.Recordset")
'set objField=Server.CreateObject("ADODB.field")

'Initialise the strSQL variable with an SQL statement to query the
database
sSQL3 = "SELECT Files.* FROM Files WHERE ID=" & RecordNo3

Set oRS3 = Conn3.Execute(sSQL3)

fileName = oRS3("File Name")
fileSize = oRS3("File Size")
'fileData = oRS3(fileName).GetChunk(fileSize)
'fileData =
"/AHousingHelpCentre/LoadPhoto.asp?ID=oRS("ID").Value"
'fileData = objField.GetChunk(size)
contentType = oRS3("Content Type")
Click to expand...
Click to expand...
Click to expand...
 
It's working ok ! I Know I'm being a pain in the butt, but do you know how to
create a second connection so I can upload the FileDB_bak.mdb on the server
because right now I'm getting the following error msg:

The Microsoft Jet database engine cannot open the file
'\\www50.brinkster.com\EmilyLeeParsons\db\FileDB_bak.mdb'. It is already
opened exclusively by another user, or you need permission to view its data

Thanks Emma

Douglas J. Steele said:
I missed the fact that the opening quote is missing!

sSQL3 = "INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb' " & _
"([File Name], [File Size], [File Data]) " & _
"SELECT [File Name], [File Size], [File Data] FROM Files WHERE ID=" &
RecordNo3



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Emma said:
Hi everything I do stops on the INTO word here's more of my code maybe
this
will help?

<% 'Dimension variables
Dim Conn3, Conn4 'Holds the Database Connection Object
Dim oRS3 'Holds the recordset for the record to be inserted
Dim sSQL3 'Holds the SQL query to query the database
Dim RecordNo3 'Holds the record number to be inserted

'Read in the record number to be inserted
RecordNo3 = CLng(Request.QueryString("ID"))

'Create an ADO connection object
Set Conn3 = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less
connection
Conn3.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("\EmilyLeeParsons\db\FileDB.mdb")

sSQL3 = INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb' " & _
"([File Name], [File Size], [File Data]) " & _
"SELECT [File Name], [File Size], [File Data] FROM Files WHERE ID=" &
RecordNo3


Conn3.Execute(sSQL3)

'Reset server objects
oRS3.Close
Set oRS3 = Nothing
Set Conn3 = Nothing
oRS4.Close
Set oRS4 = Nothing
Set Conn4 = Nothing




Douglas J. Steele said:
The (*) is incorrect: it either needs to be a list of field names, or
left
out.

sSQL3 = INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb' " & _
"SELECT Files.* FROM Files WHERE ID=" & RecordNo3

although I would recommend including the field names:

sSQL3 = INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb' " & _
"(FIeld1, Field2, Field3) " & _
"SELECT Field1, Field2, Field3 FROM Files WHERE ID=" & RecordNo3

Another alternative is

sSQL3 = INSERT INTO [;Database=\EmilyLeeParsons\db\FileDB_bak.mdb].Files
" &
_
"(FIeld1, Field2, Field3) " & _
"SELECT Field1, Field2, Field3 FROM Files WHERE ID=" & RecordNo3



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ok I've altered the statement a little bit trying to copy the whole row
however I keep getting an error message and it points to the word INTO
here
it is:

sSQL3 = INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb'(*)
SELECT
Files.* FROM Files WHERE ID=" & RecordNo3

Set oRS3 = Conn3.Execute(sSQL3)

:

I have never seen a statement like this before with an INSERT and
SELECT
together. I've been trying it using:

sSQL3 = INSERT * INTO '\db\FileDB_bak.mdb' VALUES SELECT Files.* FROM
Files
WHERE ID=" & RecordNo3

Set oRS3 = Conn3.Execute(sSQL3)

Can you offer me some advice I'm lost

To insert the whole row?

:

Hi,
have you tried to use append or update query? Much easier!

Insert into Files IN 'c:\mymdb' ([File Name]) Select [File Name]
from
Files
Where ID=1

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


Hi I know this isn't the forum for coding but I'm at a standstill.
How do
I
copy a photo from one database and put it in another. I've been
reading
about
getchunks but I'm not sure that's what I need to do. I'm using
appendchunk
to
store it in the second database and that seems to be working fine.
It's
just
how do I get the photo out of the first database. Here is my
convoluted
code,
any help would be appreciated:

'Read in the record number to be inserted
RecordNo3 = CLng(Request.QueryString("ID"))

'Create an ADO connection object
Set Conn3 = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a
DSN-less
connection
Conn3.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("\db\FileDB.mdb")

'Create an ADO recordset object
Set oRS3 = Server.CreateObject("ADODB.Recordset")
'set objField=Server.CreateObject("ADODB.field")

'Initialise the strSQL variable with an SQL statement to query the
database
sSQL3 = "SELECT Files.* FROM Files WHERE ID=" & RecordNo3

Set oRS3 = Conn3.Execute(sSQL3)

fileName = oRS3("File Name")
fileSize = oRS3("File Size")
'fileData = oRS3(fileName).GetChunk(fileSize)
'fileData =
"/AHousingHelpCentre/LoadPhoto.asp?ID=oRS("ID").Value"
'fileData = objField.GetChunk(size)
contentType = oRS3("Content Type")
Click to expand...
Click to expand...
Click to expand...
 
Not sure why you need a second connection. Uploading data files shouldn't
require a connection.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Emma said:
It's working ok ! I Know I'm being a pain in the butt, but do you know how
to
create a second connection so I can upload the FileDB_bak.mdb on the
server
because right now I'm getting the following error msg:

The Microsoft Jet database engine cannot open the file
'\\www50.brinkster.com\EmilyLeeParsons\db\FileDB_bak.mdb'. It is already
opened exclusively by another user, or you need permission to view its
data

Thanks Emma

Douglas J. Steele said:
I missed the fact that the opening quote is missing!

sSQL3 = "INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb' " & _
"([File Name], [File Size], [File Data]) " & _
"SELECT [File Name], [File Size], [File Data] FROM Files WHERE ID=" &
RecordNo3



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Emma said:
Hi everything I do stops on the INTO word here's more of my code maybe
this
will help?

<% 'Dimension variables
Dim Conn3, Conn4 'Holds the Database Connection Object
Dim oRS3 'Holds the recordset for the record to be inserted
Dim sSQL3 'Holds the SQL query to query the database
Dim RecordNo3 'Holds the record number to be inserted

'Read in the record number to be inserted
RecordNo3 = CLng(Request.QueryString("ID"))

'Create an ADO connection object
Set Conn3 = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less
connection
Conn3.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("\EmilyLeeParsons\db\FileDB.mdb")

sSQL3 = INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb' " & _
"([File Name], [File Size], [File Data]) " & _
"SELECT [File Name], [File Size], [File Data] FROM Files WHERE ID=" &
RecordNo3


Conn3.Execute(sSQL3)

'Reset server objects
oRS3.Close
Set oRS3 = Nothing
Set Conn3 = Nothing
oRS4.Close
Set oRS4 = Nothing
Set Conn4 = Nothing




:

The (*) is incorrect: it either needs to be a list of field names, or
left
out.

sSQL3 = INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb' " &
_
"SELECT Files.* FROM Files WHERE ID=" & RecordNo3

although I would recommend including the field names:

sSQL3 = INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb' " &
_
"(FIeld1, Field2, Field3) " & _
"SELECT Field1, Field2, Field3 FROM Files WHERE ID=" & RecordNo3

Another alternative is

sSQL3 = INSERT INTO
[;Database=\EmilyLeeParsons\db\FileDB_bak.mdb].Files
" &
_
"(FIeld1, Field2, Field3) " & _
"SELECT Field1, Field2, Field3 FROM Files WHERE ID=" & RecordNo3



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ok I've altered the statement a little bit trying to copy the whole
row
however I keep getting an error message and it points to the word
INTO
here
it is:

sSQL3 = INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb'(*)
SELECT
Files.* FROM Files WHERE ID=" & RecordNo3

Set oRS3 = Conn3.Execute(sSQL3)

:

I have never seen a statement like this before with an INSERT and
SELECT
together. I've been trying it using:

sSQL3 = INSERT * INTO '\db\FileDB_bak.mdb' VALUES SELECT Files.*
FROM
Files
WHERE ID=" & RecordNo3

Set oRS3 = Conn3.Execute(sSQL3)

Can you offer me some advice I'm lost

To insert the whole row?

:

Hi,
have you tried to use append or update query? Much easier!

Insert into Files IN 'c:\mymdb' ([File Name]) Select [File Name]
from
Files
Where ID=1

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


Hi I know this isn't the forum for coding but I'm at a
standstill.
How do
I
copy a photo from one database and put it in another. I've been
reading
about
getchunks but I'm not sure that's what I need to do. I'm using
appendchunk
to
store it in the second database and that seems to be working
fine.
It's
just
how do I get the photo out of the first database. Here is my
convoluted
code,
any help would be appreciated:

'Read in the record number to be inserted
RecordNo3 = CLng(Request.QueryString("ID"))

'Create an ADO connection object
Set Conn3 = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a
DSN-less
connection
Conn3.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("\db\FileDB.mdb")

'Create an ADO recordset object
Set oRS3 = Server.CreateObject("ADODB.Recordset")
'set objField=Server.CreateObject("ADODB.field")

'Initialise the strSQL variable with an SQL statement to query
the
database
sSQL3 = "SELECT Files.* FROM Files WHERE ID=" & RecordNo3

Set oRS3 = Conn3.Execute(sSQL3)

fileName = oRS3("File Name")
fileSize = oRS3("File Size")
'fileData = oRS3(fileName).GetChunk(fileSize)
'fileData =
"/AHousingHelpCentre/LoadPhoto.asp?ID=oRS("ID").Value"
'fileData = objField.GetChunk(size)
contentType = oRS3("Content Type")
Click to expand...
Click to expand...
Click to expand...
 
Hi Douglas, you were right I didn't need a second connection. Apparently I
had the wrong location for the 2nd DB. Thanks for all your help! I couldn't
have done it without you!

Douglas J. Steele said:
Not sure why you need a second connection. Uploading data files shouldn't
require a connection.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Emma said:
It's working ok ! I Know I'm being a pain in the butt, but do you know how
to
create a second connection so I can upload the FileDB_bak.mdb on the
server
because right now I'm getting the following error msg:

The Microsoft Jet database engine cannot open the file
'\\www50.brinkster.com\EmilyLeeParsons\db\FileDB_bak.mdb'. It is already
opened exclusively by another user, or you need permission to view its
data

Thanks Emma

Douglas J. Steele said:
I missed the fact that the opening quote is missing!

sSQL3 = "INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb' " & _
"([File Name], [File Size], [File Data]) " & _
"SELECT [File Name], [File Size], [File Data] FROM Files WHERE ID=" &
RecordNo3



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi everything I do stops on the INTO word here's more of my code maybe
this
will help?

<% 'Dimension variables
Dim Conn3, Conn4 'Holds the Database Connection Object
Dim oRS3 'Holds the recordset for the record to be inserted
Dim sSQL3 'Holds the SQL query to query the database
Dim RecordNo3 'Holds the record number to be inserted

'Read in the record number to be inserted
RecordNo3 = CLng(Request.QueryString("ID"))

'Create an ADO connection object
Set Conn3 = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less
connection
Conn3.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("\EmilyLeeParsons\db\FileDB.mdb")

sSQL3 = INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb' " & _
"([File Name], [File Size], [File Data]) " & _
"SELECT [File Name], [File Size], [File Data] FROM Files WHERE ID=" &
RecordNo3


Conn3.Execute(sSQL3)

'Reset server objects
oRS3.Close
Set oRS3 = Nothing
Set Conn3 = Nothing
oRS4.Close
Set oRS4 = Nothing
Set Conn4 = Nothing




:

The (*) is incorrect: it either needs to be a list of field names, or
left
out.

sSQL3 = INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb' " &
_
"SELECT Files.* FROM Files WHERE ID=" & RecordNo3

although I would recommend including the field names:

sSQL3 = INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb' " &
_
"(FIeld1, Field2, Field3) " & _
"SELECT Field1, Field2, Field3 FROM Files WHERE ID=" & RecordNo3

Another alternative is

sSQL3 = INSERT INTO
[;Database=\EmilyLeeParsons\db\FileDB_bak.mdb].Files
" &
_
"(FIeld1, Field2, Field3) " & _
"SELECT Field1, Field2, Field3 FROM Files WHERE ID=" & RecordNo3



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ok I've altered the statement a little bit trying to copy the whole
row
however I keep getting an error message and it points to the word
INTO
here
it is:

sSQL3 = INSERT INTO Files IN '\EmilyLeeParsons\db\FileDB_bak.mdb'(*)
SELECT
Files.* FROM Files WHERE ID=" & RecordNo3

Set oRS3 = Conn3.Execute(sSQL3)

:

I have never seen a statement like this before with an INSERT and
SELECT
together. I've been trying it using:

sSQL3 = INSERT * INTO '\db\FileDB_bak.mdb' VALUES SELECT Files.*
FROM
Files
WHERE ID=" & RecordNo3

Set oRS3 = Conn3.Execute(sSQL3)

Can you offer me some advice I'm lost

To insert the whole row?

:

Hi,
have you tried to use append or update query? Much easier!

Insert into Files IN 'c:\mymdb' ([File Name]) Select [File Name]
from
Files
Where ID=1

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


Hi I know this isn't the forum for coding but I'm at a
standstill.
How do
I
copy a photo from one database and put it in another. I've been
reading
about
getchunks but I'm not sure that's what I need to do. I'm using
appendchunk
to
store it in the second database and that seems to be working
fine.
It's
just
how do I get the photo out of the first database. Here is my
convoluted
code,
any help would be appreciated:

'Read in the record number to be inserted
RecordNo3 = CLng(Request.QueryString("ID"))

'Create an ADO connection object
Set Conn3 = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a
DSN-less
connection
Conn3.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("\db\FileDB.mdb")

'Create an ADO recordset object
Set oRS3 = Server.CreateObject("ADODB.Recordset")
'set objField=Server.CreateObject("ADODB.field")

'Initialise the strSQL variable with an SQL statement to query
the
database
sSQL3 = "SELECT Files.* FROM Files WHERE ID=" & RecordNo3

Set oRS3 = Conn3.Execute(sSQL3)

fileName = oRS3("File Name")
fileSize = oRS3("File Size")
'fileData = oRS3(fileName).GetChunk(fileSize)
'fileData =
"/AHousingHelpCentre/LoadPhoto.asp?ID=oRS("ID").Value"
'fileData = objField.GetChunk(size)
contentType = oRS3("Content Type")
Click to expand...
Click to expand...
Click to expand...
 
Back
Top