please please help

[pamo]

Hi all. Am new to this site and to SQL so treat me with !!!

I have imported data from a MS ACCESS file. Not all of the data from the MS access table has been transfered to my sql tables. Septemebers data does not appear at all and Nov and Dec's data only shows data from 1st - 9th december !!! it should be from 1st - 31st dec. Lookin at the code i cannot work out why this is. I will paste the whole code in as i dont know which part of the code is causing the problem..

What i was thinking was to do a count statement which uses the msgbox function to pop up when a months data has been entered which tells me the total number of rows added for that month . This is So i can see whether the data has gone in, but dont know how to or where to place it

PLEASE PLEASE HELP

THE B****RD CODE
dim varLOOP
dim Sqlstring
dim SqlInsert
dim Conn
dim Rs
dim ConnWrite
dim objcmd
dim strconn
dim LpCount
dim var_day
dim var_month
dim var_year
dim var_logon_date
dim var_logoff_date

on error resume next

'***************************************************************************************************
*******************
'set variable
varLOOP = 1

'start loop for month value
Do until varLOOP =13
'***************************************************************************************************
*******************
'msgbox varLOOP

'build connection string to required DB
'work out month from loop counter
DBMonth = varLOOP

'if month is less than 10 then add a zero
if DBMonth < 10 then
DBMonth = &quot;0&quot; & DBMonth
end if

DBYear = &quot;2002&quot;

'create ms access db filename
accessDB = &quot;histcrec&quot; & DBMonth & DBYear & &quot;.mdb&quot;

strconn=&quot;PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=&quot;
strconn=strconn & &quot;Q:\rtarchdata\&quot; & accessDB & &quot;;&quot;

'msgbox accessDB

'Build SQLstring

sqlstring = &quot;select * from agentlogondata&quot;

set Conn = createobject(&quot;adodb.connection&quot
set Rs = createobject(&quot;adodb.recordset&quot
Conn.open = strconn
Rs.open SqlString, Conn
LpCount = 0

err.clear

if Rs.eof then

set ConnWrite = createobject(&quot;adodb.connection&quot

ConnWrite.open = &quot;DSN=CallScan&quot;

Sqlinsert = SqlInsert & &quot;insert into agentlogondataerr (agent_id)&quot;
Sqlinsert = SqlInsert & &quot;values ('0')&quot;

ConnWrite.Execute(SQLinsert)

else:

set ConnWrite = createobject(&quot;adodb.connection&quot
ConnWrite.open = &quot;DSN=CallScan&quot;


do until Rs.eof

'Build a SQL insert string
SqlInsert = &quot;&quot;
Sqlinsert = SqlInsert & &quot;(agent_id, group_id, turret_id, logon_date, logoff_date, &quot;

Sqlinsert = SqlInsert & &quot;logon_ddmmyyyy, logon_hhmmss, logoff_ddmmyyyy, logoff_hhmmss)&quot;

Sqlinsert = SqlInsert & &quot; values ('&quot; & rs(&quot;agent_id&quot & &quot;', '&quot; & rs(&quot;group_id&quot & &quot;', '&quot; & rs(&quot;turret_id&quot & &quot;', '&quot; & rs(&quot;logon_date&quot & &quot;', '&quot; & rs(&quot;logoff_date&quot & &quot;', &quot;


'build logon date
var_day = datepart (&quot;d&quot;, rs(&quot;logon_date&quot)
var_month = datepart (&quot;m&quot;, rs(&quot;logon_date&quot)
var_year = datepart (&quot;yyyy&quot;, rs(&quot;logon_date&quot)

Var_month = monthname(var_month)

var_logon_date = var_day & &quot; &quot; & var_month & &quot; &quot; & var_year

'logon date

sqlinsert = sqlinsert & &quot;'&quot; & var_logon_date & &quot;', &quot;

'logon date
sqlinsert = sqlinsert & &quot;'&quot; & datepart(&quot;h&quot;, rs(&quot;logon_date&quot) & &quot;:&quot; & datepart(&quot;n&quot;, rs(&quot;logon_date&quot) & &quot;:&quot; & datepart(&quot;s&quot;, rs(&quot;logon_date&quot) & &quot;', &quot;

'build logoff date
var_day = datepart (&quot;d&quot;, rs(&quot;logoff_date&quot)
var_month = datepart (&quot;m&quot;, rs(&quot;logoff_date&quot)
var_year = datepart (&quot;yyyy&quot;, rs(&quot;logoff_date&quot)

var_month = monthname(var_month)

var_logoff_Date = var_day & &quot; &quot; & var_month & &quot; &quot; & var_year


sqlinsert = sqlinsert & &quot;'&quot; & var_logoff_date & &quot;', &quot;

sqlinsert = sqlinsert & &quot;'&quot; & datepart(&quot;h&quot;, rs(&quot;logoff_date&quot) & &quot;:&quot; & datepart (&quot;n&quot;, rs(&quot;logoff_date&quot) & &quot;:&quot; & datepart (&quot;s&quot;, rs(&quot;logoff_date&quot) & &quot;')&quot;




ConnWrite.Execute(&quot;Insert into agentlogondata &quot; & SQLinsert)

if err.number <> 0 then

ConnWrite.Execute(&quot;Insert into agentlogondataerr &quot; & SQLinsert)

err.clear

end if
LPcount = LPcount + 1
Rs.movenext
loop
ConnWrite.close
set ConnWrite = nothing
end if
Rs.close
Conn.close
'***************************************************************************************************
*******************
'increment by one
varLOOP = varLOOP + 1

'end main loop
Loop
'***************************************************************************************************
*******************
'destroy connections
set Rs = nothing
set Conn = nothing

 

[grega]

Post a couple of line of the datafile in Q:\rtarchdata
Greg.

 

[tlbroadbent]

You really should post this question in the SQL Server forum if you are importing to SQL Server. Read faq220-1073 - &quot;What is ANSI SQL? Why This Forum?&quot; to find out why.

If you post in the SQL Server forum, you should answer the following questions.

What is the data type of the columns logon_ddmmyyyy and logoff_ddmmyyyy?
What is the default language for the server and the login?
What is the language of the PC running the program?

In addition, provide a few samples of the Access rows as suggested by Greg. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm