MAJOR PROBLEM!!!

[Hamish]

I have a major problem. I have done a simple query:

SELECT tblDispense.DispenseID, tblDispense.DispenseDate, tblDispenseItem.DispensedAs,tblDepartments.DeptName
FROM tblDispense INNER JOIN
tblDispenseItem ON tblDispense.DepartmentID =
tblDispenseItem.DepartmentID INNER JOIN tblDepartments ON tblDispenseItem.DepartmentID
= tblDepartments.DepartmentID

I then put this into a recordset. I need to be able to send all of this data
to another server with the same database, by building INSERT statments.
Does anyone know how i can do it?
I am thinking of looping through the recordset and marking the start and
end of each table in query. Then building INSERT Statements based on what
is within the loop. Then i would move the next table in the query and build
INSERT statments out of that.
Is this the way to do it?

Please Help

Thanks

[Arthur Wood]

Do ALL of the related tables that you use in this query ALREADY exist on the
other Database?

Do those tables contain the necessary Data so that the JOINS will in fact
be valid?

(by the way, this is NOT a simple query - you are carrying out TWO INNER
JOINS - a SIMPLE query would be "Select * from tblDispense")

If the Tables DO NOT already exist in the traget database, then you will
need to CREATE them, FIRST.

If the tables DO exist, but are not populated with the necessary data (for
tblDispenseItems and tblDepartments), then those tables MUST be populated
FIRST, before you can begin to think about moving the data from your query.

Finally, since you refer to fields from all three tables in your query, this
query WILL NOT be able to be used to INSERT records into another Table, whether
that table is IN the current database, or another database. This kind of
a query will ALWAYS create a recordset that is NON_UPDATEABLE, and also cannot
be used to create records in another table.

Arthur Wood



"Hamish" <hamishlucas@blueyonder.co.uk> wrote:
>
>I have a major problem. I have done a simple query:
>
>SELECT tblDispense.DispenseID, tblDispense.DispenseDate, tblDispenseItem.DispensedAs,tblDepartments.DeptName
>FROM tblDispense INNER JOIN
>tblDispenseItem ON tblDispense.DepartmentID =
>tblDispenseItem.DepartmentID INNER JOIN tblDepartments ON tblDispenseItem.DepartmentID
>= tblDepartments.DepartmentID
>
>I then put this into a recordset. I need to be able to send all of this
data
>to another server with the same database, by building INSERT statments.

>Does anyone know how i can do it?
>I am thinking of looping through the recordset and marking the start and
>end of each table in query. Then building INSERT Statements based on what
>is within the loop. Then i would move the next table in the query and build
>INSERT statments out of that.
>Is this the way to do it?
>
>Please Help
>
>Thanks

[challis]

This code is frm a similar utility I wrote for a single table. you can easily
modify it to create it from views and joined recordsets....
the principal remains the same : (use ADOX if you need to determine the original
view structure and the like) !! the objects need to exist as expected in
the destination database, else need to be created.



Private Sub ScriptDataItems( _
ByRef strTableName As String, _
ByRef cnn As ADODB.Connection, _
ByRef lnghFile As Long)

Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
Dim strValues As String
Dim strFields As String
Dim strInsert As String

Set rst = New ADODB.Recordset

rst.Open _
"Select * from " & strTableName, _
cnn, _
adOpenStatic

If Not (rst.BOF And rst.EOF) Then
rst.MoveFirst

Print #lnghFile, "/*"
Print #lnghFile, "Statements to insert data into " & strTableName
Print #lnghFile, "*/"
Print #lnghFile, "DELETE FROM " & strTableName
Print #lnghFile, "GO"
' determine if there is an identity column
Print #lnghFile, "IF EXISTS("
Print #lnghFile, " SELECT * FROM INFORMATION_SCHEMA.Columns"
Print #lnghFile, " WHERE "
Print #lnghFile, " COLUMNPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA)
+ '.' + QUOTENAME(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1 AND"
Print #lnghFile, " OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA)
+ '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0 AND"
Print #lnghFile, " TABLE_SCHEMA = 'dbo' AND"
Print #lnghFile, " TABLE_NAME = '" & strTableName & "')"
Print #lnghFile, "SET IDENTITY_INSERT " & strTableName & " ON"
Print #lnghFile, "GO"

Do Until rst.EOF

strFields = "("
strValues = "("

For Each fld In rst.Fields
If Not IsNull(fld.Value) Then
strFields = strFields & fld.Name & ", "
strValues = strValues & GetDelimitedFieldVal(fld) & ", "
End If
Next fld

strFields = Left$(strFields, Len(strFields) - 2) & ")"
strValues = Left$(strValues, Len(strValues) - 2) & ")"

strInsert = _
"INSERT INTO [dbo].[" & strTableName & "] " & strFields & " VALUES
" & strValues

Print #lnghFile, strInsert

rst.MoveNext
Loop

Print #lnghFile, "GO"
' determine if there is an identity column
Print #lnghFile, "IF EXISTS("
Print #lnghFile, " SELECT * FROM INFORMATION_SCHEMA.Columns"
Print #lnghFile, " WHERE "
Print #lnghFile, " COLUMNPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA)
+ '.' + QUOTENAME(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1 AND"
Print #lnghFile, " OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA)
+ '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0 AND"
Print #lnghFile, " TABLE_SCHEMA = 'dbo' AND"
Print #lnghFile, " TABLE_NAME = '" & strTableName & "')"
Print #lnghFile, "SET IDENTITY_INSERT " & strTableName & " OFF"
Print #lnghFile, "GO"
Print #lnghFile, ""

End If

Set fld = Nothing
Set rst = Nothing

End Sub ' ScriptDataItems


Private Function GetDelimitedFieldVal( _
ByRef fld As ADODB.Field) _
As String

Dim str As String

Select Case fld.Type

Case _
ADODB.DataTypeEnum.adInteger, _
ADODB.DataTypeEnum.adBigInt, _
ADODB.DataTypeEnum.adDecimal, _
ADODB.DataTypeEnum.adCurrency, _
ADODB.DataTypeEnum.adDouble, _
ADODB.DataTypeEnum.adNumeric, _
ADODB.DataTypeEnum.adSingle, _
ADODB.DataTypeEnum.adTinyInt, _
ADODB.DataTypeEnum.adSmallInt

str = CStr(fld.Value)

Case _
ADODB.DataTypeEnum.adDate, _
ADODB.DataTypeEnum.adDBDate, _
ADODB.adDBTimeStamp

'If fld.Value > Now Then
str = Format(Now, "'dd-mmm-yyyy'")
' Else
' str = Format(fld.Value, "'dd-mmm-yyyy'")
' End If

Case _
ADODB.DataTypeEnum.adBoolean

If fld.Value = True Then
str = "1"
Else
str = "0"
End If

Case Else

str = "'" & Replace(fld.Value, "'", "''") & "'"

End Select

GetDelimitedFieldVal = str

End Function ' GetDelimitedFieldVal






"Arthur Wood" <wooda@nospam.com> wrote:
>
>Do ALL of the related tables that you use in this query ALREADY exist on
the
>other Database?
>
>Do those tables contain the necessary Data so that the JOINS will in fact
>be valid?
>
>(by the way, this is NOT a simple query - you are carrying out TWO INNER
>JOINS - a SIMPLE query would be "Select * from tblDispense")
>
>If the Tables DO NOT already exist in the traget database, then you will
>need to CREATE them, FIRST.
>
>If the tables DO exist, but are not populated with the necessary data (for
>tblDispenseItems and tblDepartments), then those tables MUST be populated
>FIRST, before you can begin to think about moving the data from your query.
>
>Finally, since you refer to fields from all three tables in your query,
this
>query WILL NOT be able to be used to INSERT records into another Table,
whether
>that table is IN the current database, or another database. This kind of
>a query will ALWAYS create a recordset that is NON_UPDATEABLE, and also
cannot
>be used to create records in another table.
>
>Arthur Wood
>
>
>
>"Hamish" <hamishlucas@blueyonder.co.uk> wrote:
>>
>>I have a major problem. I have done a simple query:
>>
>>SELECT tblDispense.DispenseID, tblDispense.DispenseDate, tblDispenseItem.DispensedAs,tblDepartments.DeptName
>>FROM tblDispense INNER JOIN
>>tblDispenseItem ON tblDispense.DepartmentID =
>>tblDispenseItem.DepartmentID INNER JOIN tblDepartments ON tblDispenseItem.DepartmentID
>>= tblDepartments.DepartmentID
>>
>>I then put this into a recordset. I need to be able to send all of this
>data
>>to another server with the same database, by building INSERT statments.
>
>>Does anyone know how i can do it?
>>I am thinking of looping through the recordset and marking the start and
>>end of each table in query. Then building INSERT Statements based on what
>>is within the loop. Then i would move the next table in the query and build
>>INSERT statments out of that.
>>Is this the way to do it?
>>
>>Please Help
>>
>>Thanks
>

[Sue Harsevoort]

I don't think the statement in your last paragraph is correct, but maybe I
am just reading it wrong. You can insert records from a query with multiple
tables into another table, I think you can even update records in the
underlining tables through a SQL statment at least, maybe not using
rs.Update.

Sue

"Arthur Wood" <wooda@nospam.com> wrote in message
news:3dc02c6a$1@tnews.web.devx.com...
>
> Finally, since you refer to fields from all three tables in your query,
this
> query WILL NOT be able to be used to INSERT records into another Table,
whether
> that table is IN the current database, or another database. This kind of
> a query will ALWAYS create a recordset that is NON_UPDATEABLE, and also
cannot
> be used to create records in another table.
>
> Arthur Wood