Code hang

  • Thread starter Thread starter Mike
  • Start date Start date
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
 
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...
 
Back
Top