Problem with Select INTO

[Piri]

Access 97
I am trying to create a table in a local mdb from data in a table in
another mdb, without actually linking the two.
Is this possible?
I have tried the code below using the .Execute and the DoCmd.RunSQL
without success.
The error message I get with both these methods is:
..Execute = <3111-Couldn't create;no modify design permission for
table or query 'tblNewData' >
DoCmd.RunSQL = <3078-The Microsoft Jet database engine could not find
the input table or query 'tbldata'. Make sure it exists and that its
name is spelled correctly.>
The table certainly does exist.
Any help gratefully appreciated.

Piri

What I am asking for is all the data in a table called 'tbldata' in an
mdb called data.mdb located in //dataserver/data/ directory where the
Serial Number matches [SN2Check] but excludes the JobID of
[ParentJob], and place that data into (create) table called
[tblNewData ] in the local MDB.


Dim dbsTrack As Database, DataLocation as String
Dim strSQL As String

DataLocation = "//dataserver/data/data.mdb"
Fieldname = "[SerialNo]"


strSQL = "SELECT tbldata.* INTO tblNewData FROM tbldata WHERE " &
[Fieldname] & " = '" & [SN2Check] & "' and JobID <> " & [ParentJob] &
";"

Set dbsTrack = OpenDatabase(DataLocation)

'dbsTrack.Execute strSQL
'or
DoCmd.RunSQL strSQL

Exit Function

where
SN2Check is from a Public variable
JobID is a field in the tbldata
ParentJob is from a Public variable (intended to be excluded from
result)

 

[Marshall Barton]

Access 97
I am trying to create a table in a local mdb from data in a table in
another mdb, without actually linking the two.
Is this possible?
I have tried the code below using the .Execute and the DoCmd.RunSQL
without success.
The error message I get with both these methods is:
.Execute = <3111-Couldn't create;no modify design permission for
table or query 'tblNewData' >
DoCmd.RunSQL = <3078-The Microsoft Jet database engine could not find
the input table or query 'tbldata'. Make sure it exists and that its
name is spelled correctly.>
The table certainly does exist.
Any help gratefully appreciated.

Piri

What I am asking for is all the data in a table called 'tbldata' in an
mdb called data.mdb located in //dataserver/data/ directory where the
Serial Number matches [SN2Check] but excludes the JobID of
[ParentJob], and place that data into (create) table called
[tblNewData ] in the local MDB.


Dim dbsTrack As Database, DataLocation as String
Dim strSQL As String

DataLocation = "//dataserver/data/data.mdb"
Fieldname = "[SerialNo]"


strSQL = "SELECT tbldata.* INTO tblNewData FROM tbldata WHERE " &
[Fieldname] & " = '" & [SN2Check] & "' and JobID <> " & [ParentJob] &
";"

Set dbsTrack = OpenDatabase(DataLocation)

'dbsTrack.Execute strSQL
'or
DoCmd.RunSQL strSQL

Exit Function

where
SN2Check is from a Public variable
JobID is a field in the tbldata
ParentJob is from a Public variable (intended to be excluded from
result)

strSQL = "SELECT tbldata.* INTO tblNewData " & _
"FROM tbldata IN """ & DataLocation & _
""" WHERE SerialNo = '" & [SN2Check] & _
"' And JobID <> " & [ParentJob]

Are you sure you need to make a table? Why isn't a Select
query sufficient? If you really need to make a table and if
it will done fairly often, then you should put the table in
a temporary database so you can avoid the bloat issues of
repeatedly creating a table.

If there's a good reason why a Select query is not
appropriate, you should at least precreate the table, then
Delete any existing records and Append the desired records.

 

[Piri]

Access 97
I am trying to create a table in a local mdb from data in a table in
another mdb, without actually linking the two.
Is this possible?
I have tried the code below using the .Execute and the DoCmd.RunSQL
without success.
The error message I get with both these methods is:
.Execute = <3111-Couldn't create;no modify design permission for
table or query 'tblNewData' >
DoCmd.RunSQL = <3078-The Microsoft Jet database engine could not find
the input table or query 'tbldata'. Make sure it exists and that its
name is spelled correctly.>
The table certainly does exist.
Any help gratefully appreciated.

Piri

What I am asking for is all the data in a table called 'tbldata' in an
mdb called data.mdb located in //dataserver/data/ directory where the
Serial Number matches [SN2Check] but excludes the JobID of
[ParentJob], and place that data into (create) table called
[tblNewData ] in the local MDB.

Dim dbsTrack As Database, DataLocation as String
Dim strSQL As String

DataLocation = "//dataserver/data/data.mdb"
Fieldname = "[SerialNo]"

strSQL = "SELECT tbldata.* INTO tblNewData FROM tbldata WHERE " &
[Fieldname] & " = '" & [SN2Check] & "' and JobID <> " & [ParentJob] &
";"

Set dbsTrack = OpenDatabase(DataLocation)

'dbsTrack.Execute strSQL
'or
DoCmd.RunSQL strSQL

Exit Function

where
SN2Check is from a Public variable
JobID is a field in the tbldata
ParentJob is from a Public variable (intended to be excluded from
result)

strSQL = "SELECT tbldata.* INTO tblNewData " & _
"FROM tbldata IN """ & DataLocation & _
""" WHERE SerialNo = '" & [SN2Check] & _
"' And JobID <> " & [ParentJob]

Are you sure you need to make a table? Why isn't a Select
query sufficient? If you really need to make a table and if
it will done fairly often, then you should put the table in
a temporary database so you can avoid the bloat issues of
repeatedly creating a table.

If there's a good reason why a Select query is not
appropriate, you should at least precreate the table, then
Delete any existing records and Append the desired records.

Hello Marsh,
Thanks for your help.
The syntax you provided works fine.
I will look at the temp database idea - I do use that with another
report front end I work with.
In this case I was looking to use this data as the record source for a
continuous form but just couldn't figure out how to get it to work.
The form will only be displaying one or two records at a time (but
with numerous fields). I reckoned on testing whether there were any
records to load first and if so use the result of the query (in this
case the table result) as the form's record source. Making the table
as the record source seemed to be the best dirty fix.

Thanks again,
Piri

 

[Marshall Barton]

Access 97
I am trying to create a table in a local mdb from data in a table in
another mdb, without actually linking the two.
Is this possible?
I have tried the code below using the .Execute and the DoCmd.RunSQL
without success.
The error message I get with both these methods is:
.Execute = <3111-Couldn't create;no modify design permission for
table or query 'tblNewData' >
DoCmd.RunSQL = <3078-The Microsoft Jet database engine could not find
the input table or query 'tbldata'. Make sure it exists and that its
name is spelled correctly.>
The table certainly does exist.

What I am asking for is all the data in a table called 'tbldata' in an
mdb called data.mdb located in //dataserver/data/ directory where the
Serial Number matches [SN2Check] but excludes the JobID of
[ParentJob], and place that data into (create) table called
[tblNewData ] in the local MDB.

Dim dbsTrack As Database, DataLocation as String
Dim strSQL As String

DataLocation = "//dataserver/data/data.mdb"
Fieldname = "[SerialNo]"

strSQL = "SELECT tbldata.* INTO tblNewData FROM tbldata WHERE " &
[Fieldname] & " = '" & [SN2Check] & "' and JobID <> " & [ParentJob] &
";"

Set dbsTrack = OpenDatabase(DataLocation)

'dbsTrack.Execute strSQL
'or
DoCmd.RunSQL strSQL

Exit Function

where
SN2Check is from a Public variable
JobID is a field in the tbldata
ParentJob is from a Public variable (intended to be excluded from
result)

strSQL = "SELECT tbldata.* INTO tblNewData " & _
"FROM tbldata IN """ & DataLocation & _
""" WHERE SerialNo = '" & [SN2Check] & _
"' And JobID <> " & [ParentJob]

Are you sure you need to make a table? Why isn't a Select
query sufficient? If you really need to make a table and if
it will done fairly often, then you should put the table in
a temporary database so you can avoid the bloat issues of
repeatedly creating a table.

If there's a good reason why a Select query is not
appropriate, you should at least precreate the table, then
Delete any existing records and Append the desired records.

The syntax you provided works fine.
I will look at the temp database idea - I do use that with another
report front end I work with.
In this case I was looking to use this data as the record source for a
continuous form but just couldn't figure out how to get it to work.
The form will only be displaying one or two records at a time (but
with numerous fields). I reckoned on testing whether there were any
records to load first and if so use the result of the query (in this
case the table result) as the form's record source. Making the table
as the record source seemed to be the best dirty fix.

Everything you've said so far still sounds like a Select
query would be sufficient, espacially if the data needs to
be updatable.

 

[Piri]

Piri wrote:
Access 97
I am trying to create a table in a local mdb from data in a table in
another mdb, without actually linking the two.
Is this possible?
I have tried the code below using the .Execute and the DoCmd.RunSQL
without success.
The error message I get with both these methods is:
.Execute = <3111-Couldn't create;no modify design permission for
table or query 'tblNewData' >
DoCmd.RunSQL = <3078-The Microsoft Jet database engine could not find
the input table or query 'tbldata'. Make sure it exists and that its
name is spelled correctly.>
The table certainly does exist.
What I am asking for is all the data in a table called 'tbldata' in an
mdb called data.mdb located in //dataserver/data/ directory where the
Serial Number matches [SN2Check] but excludes the JobID of
[ParentJob], and place that data into (create) table called
[tblNewData ] in the local MDB.
Dim dbsTrack As Database, DataLocation as String
Dim strSQL As String
DataLocation = "//dataserver/data/data.mdb"
Fieldname = "[SerialNo]"
strSQL = "SELECT tbldata.* INTO tblNewData FROM tbldata WHERE " &
[Fieldname] & " = '" & [SN2Check] & "' and JobID <> " & [ParentJob] &
";"
Set dbsTrack = OpenDatabase(DataLocation)
'dbsTrack.Execute strSQL
'or
DoCmd.RunSQL strSQL
Exit Function
where
SN2Check is from a Public variable
JobID is a field in the tbldata
ParentJob is from a Public variable (intended to be excluded from
result)
strSQL = "SELECT tbldata.* INTO tblNewData " & _
"FROM tbldata IN """ & DataLocation & _
""" WHERE SerialNo = '" & [SN2Check] & _
"' And JobID <> " & [ParentJob]
Are you sure you need to make a table? Why isn't a Select
query sufficient? If you really need to make a table and if
it will done fairly often, then you should put the table in
a temporary database so you can avoid the bloat issues of
repeatedly creating a table.
If there's a good reason why a Select query is not
appropriate, you should at least precreate the table, then
Delete any existing records and Append the desired records.

The syntax you provided works fine.
I will look at the temp database idea - I do use that with another
report front end I work with.
In this case I was looking to use this data as the record source for a
continuous form but just couldn't figure out how to get it to work.
The form will only be displaying one or two records at a time (but
with numerous fields). I reckoned on testing whether there were any
records to load first and if so use the result of the query (in this
case the table result) as the form's record source. Making the table
as the record source seemed to be the best dirty fix.

Everything you've said so far still sounds like a Select
query would be sufficient, espacially if the data needs to
be updatable.

You have convinced me.
I have abandoned the make table idea and am now using the select query
as the record source for the form
I am using the form open event to set the record source for the form.
But how do I intercept the process if there are no records to show?
Am I correct in assuming that to count the result of the query and
then use it as a record source amounts to running it twice?

What I would like is for the operator to get a message like "No
history to show" if there are no records derived form the query.
Otherwise all I get is a blank form?

I appreciate you help.

Piri

 

[Marshall Barton]

I am using the form open event to set the record source for the form.
But how do I intercept the process if there are no records to show?
Am I correct in assuming that to count the result of the query and
then use it as a record source amounts to running it twice?

What I would like is for the operator to get a message like "No
history to show" if there are no records derived form the query.
Otherwise all I get is a blank form?

Add code to the form's Load event:

If Me.Recordset.Recount = 0 Then
MsgBox "No history to show"
DoCmd.Close acForm, Me.Name
End If