Code hang

M

Mike

I work on a secured network. I am required to enter a password every time my
computer locks itself or if I log off and need to log back on. The systems
administrator has all computers set to lock if inactive for 20 minutes as per
corporate policy.
Here is my problem, I am trying to automate my database to run on non peak
times, 3:00 AM, when everyone, myself included, is not present. I am still
logged on but the computer is locked. Using windows scheduler and an autoexec
macro I launch my database at 3:00 AM, I am still logged in. The code will
run for the first five lines and then halt until I unlock the computer in the
morning and then the code will continue. I do not get any errors, the code
simply halts until I unlock the computer in the morning.
Here is my code, I am linking to ODB database table that do not have primary
keys, hence that is the reason for the SENDKEYS ("{ESC}") statement.
Any help is greatly appreciated.:

Set db = CurrentDb
SendKeys ("{ESC}")
DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DRIVER=SQL
Server;SERVER=rssms2-7333-861;UID=harrimi2;APP=Microsoft Access
Components;DATABASE=dbMARS;Trusted_Connection=Yes", acTable,
"dbo.ext_pln_007_02_ddg_data", "dbo_ext_pln_007_02_ddg_data", True
SendKeys ("{ESC}")
DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DRIVER=SQL
Server;SERVER=rssms2-7333-861;UID=harrimi2;APP=Microsoft Access
Components;DATABASE=dbMARS;Trusted_Connection=Yes", acTable,
"dbo.ext_pln_007_02_nsc_data", "dbo_ext_pln_007_02_nsc_data", True
SendKeys ("{ESC}")
DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DRIVER=SQL
Server;SERVER=rssms2-7333-861;UID=harrimi2;APP=Microsoft Access
Components;DATABASE=dbMARS;Trusted_Connection=Yes", acTable,
"dbo.ext_pln_007_02_lha_data", "dbo_ext_pln_007_02_ddg_lha", True
Set db = CurrentDb
For Each tdf In db.TableDefs
If tdf.Name = "MR45" Then
db.TableDefs.Delete tdf.Name
End If
Next tdf
Set db = CurrentDb
strSQL = "SELECT distinct dbo_ext_pln_007_02_ddg_data.PROJ_CLIN_ID AS [Real
Hull], Left([dbo_ext_pln_007_02_ddg_data]![ACT_ID],10) AS Bill,
dbo_ext_pln_007_02_ddg_data.LEAD_DEPT AS [Lead Department],
Left([dbo_ext_pln_007_02_ddg_data]![DRAW_ID],1) AS [Drawing Prefix],
Left([dbo_ext_pln_007_02_ddg_data]![DRAW_ID],12) AS Drawing,
dbo_ext_pln_007_02_ddg_data.POS_NO AS FN, dbo_ext_pln_007_02_ddg_data.MAT_ID
AS LSN, dbo_ext_pln_007_02_ddg_data.EID AS [Bill Indenture],
dbo_ext_pln_007_02_ddg_data.DRL_ROOM_ID AS [Funct Compt],
dbo_ext_pln_007_02_ddg_data.BILL_INDENTURE AS Remarks,
dbo_ext_pln_007_02_ddg_data.HOTWORK AS [H/W] INTO MR45 FROM
dbo_ext_pln_007_02_ddg_data " & _
"WHERE (((dbo_ext_pln_007_02_ddg_data.PROJ_CLIN_ID)='5215' Or
(dbo_ext_pln_007_02_ddg_data.PROJ_CLIN_ID)='5216') AND
((dbo_ext_pln_007_02_ddg_data.LEAD_DEPT)='35'));"
db.Execute strSQL
strSQL = "INSERT INTO MR45 ( [Real Hull], Bill, [Lead Department], [Drawing
Prefix], Drawing, FN, LSN, [Bill Indenture], [Funct Compt], Remarks, [H/W] )
SELECT distinct dbo_ext_pln_007_02_lha_data.PROJ_CLIN_ID AS [Real Hull],
Left([dbo_ext_pln_007_02_lha_data]![ACT_ID],10) AS Bill,
dbo_ext_pln_007_02_lha_data.LEAD_DEPT AS [Lead Department],
Left([dbo_ext_pln_007_02_lha_data]![DRAW_ID],1) AS [Drawing Prefix],
Left([dbo_ext_pln_007_02_lha_data]![DRAW_ID],12) AS Drawing,
dbo_ext_pln_007_02_lha_data.POS_NO AS FN, dbo_ext_pln_007_02_lha_data.MAT_ID
AS LSN, dbo_ext_pln_007_02_lha_data.BILL_INDENTURE AS [Bill Indenture],
dbo_ext_pln_007_02_lha_data.DRL_ROOM_ID AS [Funct Compt],
dbo_ext_pln_007_02_lha_data.EID AS Remarks,
dbo_ext_pln_007_02_lha_data.HOTWORK AS [H/W] FROM dbo_ext_pln_007_02_lha_data
" & _
"WHERE (((dbo_ext_pln_007_02_lha_data.LEAD_DEPT)='35'));"
db.Execute strSQL
strSQL = "INSERT INTO MR45 ( [Real Hull], Bill, [Lead Department], [Drawing
Prefix], Drawing, FN, LSN, [Bill Indenture], [Funct Compt], Remarks, [H/W] )
SELECT distinct dbo_ext_pln_007_02_nsc_data.PROJ_CLIN_ID AS [Real Hull],
Left([dbo_ext_pln_007_02_nsc_data]![ACT_ID],10) AS Bill,
dbo_ext_pln_007_02_nsc_data.LEAD_DEPT AS [Lead Department],
Left([dbo_ext_pln_007_02_nsc_data]![DRAW_ID],1) AS [Drawing Prefix],
Left([dbo_ext_pln_007_02_nsc_data]![DRAW_ID],12) AS Drawing,
dbo_ext_pln_007_02_nsc_data.POS_NO AS FN, dbo_ext_pln_007_02_nsc_data.MAT_ID
AS LSN, dbo_ext_pln_007_02_nsc_data.BILL_INDENTURE AS [Bill Indenture],
dbo_ext_pln_007_02_nsc_data.DRL_ROOM_ID AS [Funct Compt],
dbo_ext_pln_007_02_nsc_data.EID AS Remarks,
dbo_ext_pln_007_02_nsc_data.HOTWORK AS [H/W] FROM dbo_ext_pln_007_02_nsc_data
" & _
"WHERE (((dbo_ext_pln_007_02_nsc_data.PROJ_CLIN_ID)='4913') AND
((dbo_ext_pln_007_02_nsc_data.LEAD_DEPT)='35'));"
db.Execute strSQL
 
A

Arvin Meyer [MVP]

There are methods other than SendKeys that should be able to work for you.
Exactly what are you trying to accomplish? Also, where exactly does it stop?
You can add a:

Debug.Print Time

statement, at various points and see exactly where it's hanging.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


Mike said:
I work on a secured network. I am required to enter a password every time
my
computer locks itself or if I log off and need to log back on. The systems
administrator has all computers set to lock if inactive for 20 minutes as
per
corporate policy.
Here is my problem, I am trying to automate my database to run on non peak
times, 3:00 AM, when everyone, myself included, is not present. I am still
logged on but the computer is locked. Using windows scheduler and an
autoexec
macro I launch my database at 3:00 AM, I am still logged in. The code will
run for the first five lines and then halt until I unlock the computer in
the
morning and then the code will continue. I do not get any errors, the code
simply halts until I unlock the computer in the morning.
Here is my code, I am linking to ODB database table that do not have
primary
keys, hence that is the reason for the SENDKEYS ("{ESC}") statement.
Any help is greatly appreciated.:

Set db = CurrentDb
SendKeys ("{ESC}")
DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DRIVER=SQL
Server;SERVER=rssms2-7333-861;UID=harrimi2;APP=Microsoft Access
Components;DATABASE=dbMARS;Trusted_Connection=Yes", acTable,
"dbo.ext_pln_007_02_ddg_data", "dbo_ext_pln_007_02_ddg_data", True
SendKeys ("{ESC}")
DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DRIVER=SQL
Server;SERVER=rssms2-7333-861;UID=harrimi2;APP=Microsoft Access
Components;DATABASE=dbMARS;Trusted_Connection=Yes", acTable,
"dbo.ext_pln_007_02_nsc_data", "dbo_ext_pln_007_02_nsc_data", True
SendKeys ("{ESC}")
DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DRIVER=SQL
Server;SERVER=rssms2-7333-861;UID=harrimi2;APP=Microsoft Access
Components;DATABASE=dbMARS;Trusted_Connection=Yes", acTable,
"dbo.ext_pln_007_02_lha_data", "dbo_ext_pln_007_02_ddg_lha", True
Set db = CurrentDb
For Each tdf In db.TableDefs
If tdf.Name = "MR45" Then
db.TableDefs.Delete tdf.Name
End If
Next tdf
Set db = CurrentDb
strSQL = "SELECT distinct dbo_ext_pln_007_02_ddg_data.PROJ_CLIN_ID AS
[Real
Hull], Left([dbo_ext_pln_007_02_ddg_data]![ACT_ID],10) AS Bill,
dbo_ext_pln_007_02_ddg_data.LEAD_DEPT AS [Lead Department],
Left([dbo_ext_pln_007_02_ddg_data]![DRAW_ID],1) AS [Drawing Prefix],
Left([dbo_ext_pln_007_02_ddg_data]![DRAW_ID],12) AS Drawing,
dbo_ext_pln_007_02_ddg_data.POS_NO AS FN,
dbo_ext_pln_007_02_ddg_data.MAT_ID
AS LSN, dbo_ext_pln_007_02_ddg_data.EID AS [Bill Indenture],
dbo_ext_pln_007_02_ddg_data.DRL_ROOM_ID AS [Funct Compt],
dbo_ext_pln_007_02_ddg_data.BILL_INDENTURE AS Remarks,
dbo_ext_pln_007_02_ddg_data.HOTWORK AS [H/W] INTO MR45 FROM
dbo_ext_pln_007_02_ddg_data " & _
"WHERE (((dbo_ext_pln_007_02_ddg_data.PROJ_CLIN_ID)='5215' Or
(dbo_ext_pln_007_02_ddg_data.PROJ_CLIN_ID)='5216') AND
((dbo_ext_pln_007_02_ddg_data.LEAD_DEPT)='35'));"
db.Execute strSQL
strSQL = "INSERT INTO MR45 ( [Real Hull], Bill, [Lead Department],
[Drawing
Prefix], Drawing, FN, LSN, [Bill Indenture], [Funct Compt], Remarks,
[H/W] )
SELECT distinct dbo_ext_pln_007_02_lha_data.PROJ_CLIN_ID AS [Real Hull],
Left([dbo_ext_pln_007_02_lha_data]![ACT_ID],10) AS Bill,
dbo_ext_pln_007_02_lha_data.LEAD_DEPT AS [Lead Department],
Left([dbo_ext_pln_007_02_lha_data]![DRAW_ID],1) AS [Drawing Prefix],
Left([dbo_ext_pln_007_02_lha_data]![DRAW_ID],12) AS Drawing,
dbo_ext_pln_007_02_lha_data.POS_NO AS FN,
dbo_ext_pln_007_02_lha_data.MAT_ID
AS LSN, dbo_ext_pln_007_02_lha_data.BILL_INDENTURE AS [Bill Indenture],
dbo_ext_pln_007_02_lha_data.DRL_ROOM_ID AS [Funct Compt],
dbo_ext_pln_007_02_lha_data.EID AS Remarks,
dbo_ext_pln_007_02_lha_data.HOTWORK AS [H/W] FROM
dbo_ext_pln_007_02_lha_data
" & _
"WHERE (((dbo_ext_pln_007_02_lha_data.LEAD_DEPT)='35'));"
db.Execute strSQL
strSQL = "INSERT INTO MR45 ( [Real Hull], Bill, [Lead Department],
[Drawing
Prefix], Drawing, FN, LSN, [Bill Indenture], [Funct Compt], Remarks,
[H/W] )
SELECT distinct dbo_ext_pln_007_02_nsc_data.PROJ_CLIN_ID AS [Real Hull],
Left([dbo_ext_pln_007_02_nsc_data]![ACT_ID],10) AS Bill,
dbo_ext_pln_007_02_nsc_data.LEAD_DEPT AS [Lead Department],
Left([dbo_ext_pln_007_02_nsc_data]![DRAW_ID],1) AS [Drawing Prefix],
Left([dbo_ext_pln_007_02_nsc_data]![DRAW_ID],12) AS Drawing,
dbo_ext_pln_007_02_nsc_data.POS_NO AS FN,
dbo_ext_pln_007_02_nsc_data.MAT_ID
AS LSN, dbo_ext_pln_007_02_nsc_data.BILL_INDENTURE AS [Bill Indenture],
dbo_ext_pln_007_02_nsc_data.DRL_ROOM_ID AS [Funct Compt],
dbo_ext_pln_007_02_nsc_data.EID AS Remarks,
dbo_ext_pln_007_02_nsc_data.HOTWORK AS [H/W] FROM
dbo_ext_pln_007_02_nsc_data
" & _
"WHERE (((dbo_ext_pln_007_02_nsc_data.PROJ_CLIN_ID)='4913') AND
((dbo_ext_pln_007_02_nsc_data.LEAD_DEPT)='35'));"
db.Execute strSQL
Click to expand...
 
P

PieterLinden via AccessMonster.com

Mike said:
I work on a secured network. I am required to enter a password every time my
computer locks itself or if I log off and need to log back on. The systems
administrator has all computers set to lock if inactive for 20 minutes as per
corporate policy.
Here is my problem, I am trying to automate my database to run on non peak
times, 3:00 AM, when everyone, myself included, is not present. I am still
logged on but the computer is locked. Using windows scheduler and an autoexec
macro I launch my database at 3:00 AM, I am still logged in. The code will
run for the first five lines and then halt until I unlock the computer in the
morning and then the code will continue. I do not get any errors, the code
simply halts until I unlock the computer in the morning.
Here is my code, I am linking to ODB database table that do not have primary
keys, hence that is the reason for the SENDKEYS ("{ESC}") statement.
Any help is greatly appreciated.:

Set db = CurrentDb
SendKeys ("{ESC}")
DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DRIVER=SQL
Server;SERVER=rssms2-7333-861;UID=harrimi2;APP=Microsoft Access
Components;DATABASE=dbMARS;Trusted_Connection=Yes", acTable,
"dbo.ext_pln_007_02_ddg_data", "dbo_ext_pln_007_02_ddg_data", True
SendKeys ("{ESC}")
DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DRIVER=SQL
Server;SERVER=rssms2-7333-861;UID=harrimi2;APP=Microsoft Access
Components;DATABASE=dbMARS;Trusted_Connection=Yes", acTable,
"dbo.ext_pln_007_02_nsc_data", "dbo_ext_pln_007_02_nsc_data", True
SendKeys ("{ESC}")
DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DRIVER=SQL
Server;SERVER=rssms2-7333-861;UID=harrimi2;APP=Microsoft Access
Components;DATABASE=dbMARS;Trusted_Connection=Yes", acTable,
"dbo.ext_pln_007_02_lha_data", "dbo_ext_pln_007_02_ddg_lha", True
Set db = CurrentDb
For Each tdf In db.TableDefs
If tdf.Name = "MR45" Then
db.TableDefs.Delete tdf.Name
End If
Next tdf
Set db = CurrentDb
strSQL = "SELECT distinct dbo_ext_pln_007_02_ddg_data.PROJ_CLIN_ID AS [Real
Hull], Left([dbo_ext_pln_007_02_ddg_data]![ACT_ID],10) AS Bill,
dbo_ext_pln_007_02_ddg_data.LEAD_DEPT AS [Lead Department],
Left([dbo_ext_pln_007_02_ddg_data]![DRAW_ID],1) AS [Drawing Prefix],
Left([dbo_ext_pln_007_02_ddg_data]![DRAW_ID],12) AS Drawing,
dbo_ext_pln_007_02_ddg_data.POS_NO AS FN, dbo_ext_pln_007_02_ddg_data.MAT_ID
AS LSN, dbo_ext_pln_007_02_ddg_data.EID AS [Bill Indenture],
dbo_ext_pln_007_02_ddg_data.DRL_ROOM_ID AS [Funct Compt],
dbo_ext_pln_007_02_ddg_data.BILL_INDENTURE AS Remarks,
dbo_ext_pln_007_02_ddg_data.HOTWORK AS [H/W] INTO MR45 FROM
dbo_ext_pln_007_02_ddg_data " & _
"WHERE (((dbo_ext_pln_007_02_ddg_data.PROJ_CLIN_ID)='5215' Or
(dbo_ext_pln_007_02_ddg_data.PROJ_CLIN_ID)='5216') AND
((dbo_ext_pln_007_02_ddg_data.LEAD_DEPT)='35'));"
db.Execute strSQL
strSQL = "INSERT INTO MR45 ( [Real Hull], Bill, [Lead Department], [Drawing
Prefix], Drawing, FN, LSN, [Bill Indenture], [Funct Compt], Remarks, [H/W] )
SELECT distinct dbo_ext_pln_007_02_lha_data.PROJ_CLIN_ID AS [Real Hull],
Left([dbo_ext_pln_007_02_lha_data]![ACT_ID],10) AS Bill,
dbo_ext_pln_007_02_lha_data.LEAD_DEPT AS [Lead Department],
Left([dbo_ext_pln_007_02_lha_data]![DRAW_ID],1) AS [Drawing Prefix],
Left([dbo_ext_pln_007_02_lha_data]![DRAW_ID],12) AS Drawing,
dbo_ext_pln_007_02_lha_data.POS_NO AS FN, dbo_ext_pln_007_02_lha_data.MAT_ID
AS LSN, dbo_ext_pln_007_02_lha_data.BILL_INDENTURE AS [Bill Indenture],
dbo_ext_pln_007_02_lha_data.DRL_ROOM_ID AS [Funct Compt],
dbo_ext_pln_007_02_lha_data.EID AS Remarks,
dbo_ext_pln_007_02_lha_data.HOTWORK AS [H/W] FROM dbo_ext_pln_007_02_lha_data
" & _
"WHERE (((dbo_ext_pln_007_02_lha_data.LEAD_DEPT)='35'));"
db.Execute strSQL
strSQL = "INSERT INTO MR45 ( [Real Hull], Bill, [Lead Department], [Drawing
Prefix], Drawing, FN, LSN, [Bill Indenture], [Funct Compt], Remarks, [H/W] )
SELECT distinct dbo_ext_pln_007_02_nsc_data.PROJ_CLIN_ID AS [Real Hull],
Left([dbo_ext_pln_007_02_nsc_data]![ACT_ID],10) AS Bill,
dbo_ext_pln_007_02_nsc_data.LEAD_DEPT AS [Lead Department],
Left([dbo_ext_pln_007_02_nsc_data]![DRAW_ID],1) AS [Drawing Prefix],
Left([dbo_ext_pln_007_02_nsc_data]![DRAW_ID],12) AS Drawing,
dbo_ext_pln_007_02_nsc_data.POS_NO AS FN, dbo_ext_pln_007_02_nsc_data.MAT_ID
AS LSN, dbo_ext_pln_007_02_nsc_data.BILL_INDENTURE AS [Bill Indenture],
dbo_ext_pln_007_02_nsc_data.DRL_ROOM_ID AS [Funct Compt],
dbo_ext_pln_007_02_nsc_data.EID AS Remarks,
dbo_ext_pln_007_02_nsc_data.HOTWORK AS [H/W] FROM dbo_ext_pln_007_02_nsc_data
" & _
"WHERE (((dbo_ext_pln_007_02_nsc_data.PROJ_CLIN_ID)='4913') AND
((dbo_ext_pln_007_02_nsc_data.LEAD_DEPT)='35'));"
db.Execute strSQL
Click to expand...

Mike,
could you please explain what you are trying to do? There are a few things
you're doing in your code that don't make sense to me.

CurrentDB.Execute <SQL Statement>

executes an *action* query (update, delete, insert). It doesn't return any
records. So I'm not clear on the purpose of this statement. You are
intending to select a set of records, but what did you want to do with it?

If you want to process the records returned by the SELECT statement
sequentially, you would have to use a recordset to do it, something like

dim rs as DAO.recordset
set rs = CurrentDB.OpenRecordset(strSQL,dbOpenForwardOnly)
do until rs.eof
'do something with record
rs.movenext
loop

If the structure of the MR45 table does not change, you would be better off
just running a delete query against it and then inserting your records.... e.
g.,

dbengine(0)(0).Execute "DELETE * FROM MR45;", dbFailOnError
dbEngine(0)(0).Execute strInsertQuery
 
Top