VBA Copy and Paste

[Ross]

This question (what I want to do) is simple but I am giving a complete
description.

I am in the QBE grid and switch to SQL view. When this view opens, it
automatically highlights the SQL from my query.

This is what I would like to do:

1. Copy the highlighted SQL to my clipboard.
2. Open a table called tblSQLStore
3. Then Paste this SQL into a field (the only field) in this table
4. Close the table.

This is the code that I fire from a hotkey ({F7}):

'*********************
Public Function Set_Up_SQL()
'**********************
Dim strsql As String

'3. Copy in SQL
SendKeys "^c", True

'1. Empty
strsql = "DELETE tblSQLStore.SQL "
strsql = strsql & "FROM tblSQLStore;"
DoCmd.SetWarnings False
DoCmd.RunSQL strsql
DoCmd.SetWarnings True

'2. Open table
DoCmd.OpenTable ("tblSQLStore")

'3. Paste in SQL
SendKeys "^v", True

'4. Close Table
'DoCmd.Close acTable, ("tblSQLstore")

End Function

This code has worked precisely the way that I want for a years and now, is
no longer working. However, it does works on some machines, particularly
without wireless keyboards and mouse. I love my wireless keyboard and mouse.
I can’t believe that that may be the issue but it might.

The reason that I paste into the field (which is memo) is that this
preserves that format which is essential to my purpose.

ie
SELECT tblVersion.VersionNo
FROM tblVersion
ORDER BY tblVersion.VersionNo DESC;

Is there a better, more reliable way to do this? Suggestions?

 

[Ed Metcalfe]

This question (what I want to do) is simple but I am giving a complete
description.

I am in the QBE grid and switch to SQL view. When this view opens, it
automatically highlights the SQL from my query.

This is what I would like to do:

1. Copy the highlighted SQL to my clipboard.
2. Open a table called tblSQLStore
3. Then Paste this SQL into a field (the only field) in this table
4. Close the table.

This is the code that I fire from a hotkey ({F7}):

'*********************
Public Function Set_Up_SQL()
'**********************
Dim strsql As String

'3. Copy in SQL
SendKeys "^c", True

'1. Empty
strsql = "DELETE tblSQLStore.SQL "
strsql = strsql & "FROM tblSQLStore;"
DoCmd.SetWarnings False
DoCmd.RunSQL strsql
DoCmd.SetWarnings True

'2. Open table
DoCmd.OpenTable ("tblSQLStore")

'3. Paste in SQL
SendKeys "^v", True

'4. Close Table
'DoCmd.Close acTable, ("tblSQLstore")

End Function

This code has worked precisely the way that I want for a years and now, is
no longer working. However, it does works on some machines, particularly
without wireless keyboards and mouse. I love my wireless keyboard and
mouse.
I can't believe that that may be the issue but it might.

The reason that I paste into the field (which is memo) is that this
preserves that format which is essential to my purpose.

ie
SELECT tblVersion.VersionNo
FROM tblVersion
ORDER BY tblVersion.VersionNo DESC;

Is there a better, more reliable way to do this? Suggestions?

An alternative way of doing it would be to loop through the QueryDefs
collection:

Sub Untested()
Dim ThisDB as DAO.Database
Dim QDef as DAO.QueryDef
Dim rstDestination as DAO.Recordset

Set ThisDB=CurrentDB()
Set rstDestination=ThisDB.OpenRecordset("tblSQLstore", dbOpenDynaset)

ThisDB.Execute "DELETE * FROM tblSQLStore;",dbFailOnError

For Each QDef in ThisDB.QueryDefs
rstDestination.AddNew
rstDestination!SQL=QDef.SQL
rstDestination.Update
Next QDef
End Sub

Ed Metcalfe.

 

[Ed Metcalfe]

This question (what I want to do) is simple but I am giving a complete
description.

I am in the QBE grid and switch to SQL view. When this view opens, it
automatically highlights the SQL from my query.

This is what I would like to do:

1. Copy the highlighted SQL to my clipboard.
2. Open a table called tblSQLStore
3. Then Paste this SQL into a field (the only field) in this table
4. Close the table.

This is the code that I fire from a hotkey ({F7}):

'*********************
Public Function Set_Up_SQL()
'**********************
Dim strsql As String

'3. Copy in SQL
SendKeys "^c", True

'1. Empty
strsql = "DELETE tblSQLStore.SQL "
strsql = strsql & "FROM tblSQLStore;"
DoCmd.SetWarnings False
DoCmd.RunSQL strsql
DoCmd.SetWarnings True

'2. Open table
DoCmd.OpenTable ("tblSQLStore")

'3. Paste in SQL
SendKeys "^v", True

'4. Close Table
'DoCmd.Close acTable, ("tblSQLstore")

End Function

This code has worked precisely the way that I want for a years and now, is
no longer working. However, it does works on some machines, particularly
without wireless keyboards and mouse. I love my wireless keyboard and
mouse.
I can't believe that that may be the issue but it might.

The reason that I paste into the field (which is memo) is that this
preserves that format which is essential to my purpose.

ie
SELECT tblVersion.VersionNo
FROM tblVersion
ORDER BY tblVersion.VersionNo DESC;

Is there a better, more reliable way to do this? Suggestions?

BTW - The reason I'd recommend against SendKeys is you don't know which
control or application will have the focus whilst the code is running.
SendKeys is best to be avoided whenever possible.

Ed Metcalfe.

 

[Ross]

Ed,
I agree with you on sendkeys. It is for those (like me, that don't know of
a better way).

I took a look at your code and will give it a try. My only concern is that
I will loose the format which the past process preserves (i need to preserve
the format.

I will let you know what I find. Thanks for you help.

Ross

 

[Ross]

It works. Thanks dude!

Ross

An alternative way of doing it would be to loop through the QueryDefs
collection:

Sub Untested()
Dim ThisDB as DAO.Database
Dim QDef as DAO.QueryDef
Dim rstDestination as DAO.Recordset

Set ThisDB=CurrentDB()
Set rstDestination=ThisDB.OpenRecordset("tblSQLstore", dbOpenDynaset)

ThisDB.Execute "DELETE * FROM tblSQLStore;",dbFailOnError

For Each QDef in ThisDB.QueryDefs
rstDestination.AddNew
rstDestination!SQL=QDef.SQL
rstDestination.Update
Next QDef
End Sub

Ed Metcalfe.

 

[Ten]

Hi Ed,

I'm trying to basically "copy" a record and then "paste" it to the same
table (using a DAO recordset) and when I took your code, I got an error 3265

rstDestination!SQL=QDef.SQL

I have the DAO 3.6 in my reference library and I can't figure out what is
going wrong.

Any help is appreciated.

Thanks