conversion to SQL backend

[gmenon100 via AccessMonster.com]

Hi all,

I have a access project for attedance backend is SQL.

I have a mdb/vba code which needs to be converted into sql,

can anone help me formulate this:

Private Sub Command4_Click()
Dim StrSQL As String
StrSQL = "INSERT INTO Attend ( AttStudent, AttDate, AttType ) " _
& "SELECT " & Me![scrStudent] & " AS F1, #" _
& Format(Me![Text0], "mm/dd/yy") & "# AS F2, " & Me![scrAttend] & " AS F3;"
DoCmd.RunSQL StrSQL

End Sub


In short, I am trying to insert /append values from the form to a exiting
table.

Thank you in advance

Gmenon

 

[Duane Hookom]

You should take the time to provide the field data types. We assume AttDate
is a date but the others aren't known.
Try code like:
Private Sub Command4_Click()
Dim StrSQL As String
StrSQL = "INSERT INTO Attend ( AttStudent, AttDate, AttType ) " _
& "Values (" & Me![scrStudent] & " , #" _
& Format(Me![Text0], "mm/dd/yy") & "#, " & Me![scrAttend] & ")"
DoCmd.RunSQL StrSQL

End Sub

 

[gmenon100 via AccessMonster.com]

Hi Duane,

First let me thank you for your response. I am sorry, I gave incomplete
information. AttStudent is a bigint, Attdate is date, and AttType is tinyint.

I tried the below code and it gave me a runtime error '128'
The name '#09' is not permitted in this context. Only constants, expressions,
or variables allowed here. Column names not permitted.

I am a novice to ADP and would appreciate if you could elaborate on this
error. Thanks again

Gmenon

You should take the time to provide the field data types. We assume AttDate
is a date but the others aren't known.
Try code like:
Private Sub Command4_Click()
Dim StrSQL As String
StrSQL = "INSERT INTO Attend ( AttStudent, AttDate, AttType ) " _
& "Values (" & Me![scrStudent] & " , #" _
& Format(Me![Text0], "mm/dd/yy") & "#, " & Me![scrAttend] & ")"
DoCmd.RunSQL StrSQL

End Sub

Hi all,

[quoted text clipped - 20 lines]

Gmenon

 

[gmenon100 via AccessMonster.com]

Hi Duane,

The code worked when I took out the #, however, seems there is something
wrong with the AttDate command as it returns 01/01/1900 for all records. I
am not able to identify the problem.

Can you comment?

Thanks.

Gmenon

You should take the time to provide the field data types. We assume AttDate
is a date but the others aren't known.
Try code like:
Private Sub Command4_Click()
Dim StrSQL As String
StrSQL = "INSERT INTO Attend ( AttStudent, AttDate, AttType ) " _
& "Values (" & Me![scrStudent] & " , #" _
& Format(Me![Text0], "mm/dd/yy") & "#, " & Me![scrAttend] & ")"
DoCmd.RunSQL StrSQL

End Sub

Hi all,

[quoted text clipped - 20 lines]

Gmenon

 

[Douglas J. Steele]

Why did you take out the #?

If you're passing it 9/13/2006, that's going to get evaluated as
0.0034511849068180075159137970703275, not as a date.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Duane,

The code worked when I took out the #, however, seems there is something
wrong with the AttDate command as it returns 01/01/1900 for all records.
I
am not able to identify the problem.

Can you comment?

Thanks.

Gmenon

You should take the time to provide the field data types. We assume
AttDate
is a date but the others aren't known.
Try code like:
Private Sub Command4_Click()
Dim StrSQL As String
StrSQL = "INSERT INTO Attend ( AttStudent, AttDate, AttType ) " _
& "Values (" & Me![scrStudent] & " , #" _
& Format(Me![Text0], "mm/dd/yy") & "#, " & Me![scrAttend] & ")"
DoCmd.RunSQL StrSQL

End Sub

Hi all,

[quoted text clipped - 20 lines]

Gmenon

 

[gmenon100 via AccessMonster.com]

Hi Douglas,

Appreciate your response, when I add back the #, I get the following:

Runtime error 128
The name '#09' is not permitted in this context. Only constants, expressions,
or variables allowed here. Column names are not permitted.


Can you please help me formulate this code.

Appreciate your help.

Why did you take out the #?

If you're passing it 9/13/2006, that's going to get evaluated as
0.0034511849068180075159137970703275, not as a date.

Hi Duane,

[quoted text clipped - 27 lines]

 

[Douglas J. Steele]

Put a Debug.Print StrSQL

into your routine after you assign the value to StrSQL. Go to the Immediate
window (Ctrl-G) and copy-and-paste what shows up there.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Douglas,

Appreciate your response, when I add back the #, I get the following:

Runtime error 128
The name '#09' is not permitted in this context. Only constants,
expressions,
or variables allowed here. Column names are not permitted.


Can you please help me formulate this code.

Appreciate your help.

Why did you take out the #?

If you're passing it 9/13/2006, that's going to get evaluated as
0.0034511849068180075159137970703275, not as a date.

Hi Duane,

[quoted text clipped - 27 lines]

Gmenon

 

[gmenon100 via AccessMonster.com]

I am new to ADP and not sure what you want me to do. below is the code where
i added the debug. is this correct? if not please guide.

Private Sub Command4_Click()
Dim StrSQL As String
StrSQL = "INSERT INTO Attend ( AttStudent, AttDate, AttType ) " _
& "Values (" & Me![scrStudent] & " , #" _
& Format(Me![Text0], "mm/dd/yy") & "#, " & Me![scrAttend] & ")"

Debug.Print StrSQL
DoCmd.RunSQL StrSQL
End Sub

Put a Debug.Print StrSQL

into your routine after you assign the value to StrSQL. Go to the Immediate
window (Ctrl-G) and copy-and-paste what shows up there.

Hi Douglas,

[quoted text clipped - 19 lines]

 

[Douglas J. Steele]

Yes, that's what I wanted. Now when you run that code and go to the
Immediate Window (which you get to using Ctrl-G), what's been printed there?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am new to ADP and not sure what you want me to do. below is the code
where
i added the debug. is this correct? if not please guide.

Private Sub Command4_Click()
Dim StrSQL As String
StrSQL = "INSERT INTO Attend ( AttStudent, AttDate, AttType ) " _
& "Values (" & Me![scrStudent] & " , #" _
& Format(Me![Text0], "mm/dd/yy") & "#, " & Me![scrAttend] & ")"

Debug.Print StrSQL
DoCmd.RunSQL StrSQL
End Sub

Put a Debug.Print StrSQL

into your routine after you assign the value to StrSQL. Go to the
Immediate
window (Ctrl-G) and copy-and-paste what shows up there.

Hi Douglas,

[quoted text clipped - 19 lines]

Gmenon

 

[Duane Hookom]

The first posting didn't state this was an ADP. Considering that, the "#"
should be replaced by single quotes for inserting dates. "#" are mostly
unique to Access/Jet databases.


--
Duane Hookom
MS Access MVP

Yes, that's what I wanted. Now when you run that code and go to the
Immediate Window (which you get to using Ctrl-G), what's been printed
there?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am new to ADP and not sure what you want me to do. below is the code
where
i added the debug. is this correct? if not please guide.

Private Sub Command4_Click()
Dim StrSQL As String
StrSQL = "INSERT INTO Attend ( AttStudent, AttDate, AttType ) " _
& "Values (" & Me![scrStudent] & " , #" _
& Format(Me![Text0], "mm/dd/yy") & "#, " & Me![scrAttend] & ")"

Debug.Print StrSQL
DoCmd.RunSQL StrSQL
End Sub

Put a Debug.Print StrSQL

into your routine after you assign the value to StrSQL. Go to the
Immediate
window (Ctrl-G) and copy-and-paste what shows up there.

Hi Douglas,

[quoted text clipped - 19 lines]

Gmenon

 

[gmenon100 via AccessMonster.com]

Hi Duane,

You rock my friend. using the single quotes solved the problem. Now the data
is perfectly appending.


Thanks a lot for your help and guidance.

"Knowledge is the only wealth that grows when shared"

Gmenon.

The first posting didn't state this was an ADP. Considering that, the "#"
should be replaced by single quotes for inserting dates. "#" are mostly
unique to Access/Jet databases.

Yes, that's what I wanted. Now when you run that code and go to the
Immediate Window (which you get to using Ctrl-G), what's been printed

[quoted text clipped - 25 lines]

 

[gmenon100 via AccessMonster.com]

Hi Duane,

I have an ADP project maintaining attendance of employees. I made this
project on my home PC with Access 2002. This works fine. I transferred this
project to my office PC with Access 2003. The pivot table created in access
2002 does not seem to work. When I open a form in Pivot view, it gives an
error " The query could not be processed. The data provider didn't supply
any further error information."

I am not able to figure out this as the same pivot table view works on my
home pc with Access 2002.

Can you throw some light on this.

Appreciate your help.

Gmenon

The first posting didn't state this was an ADP. Considering that, the "#"
should be replaced by single quotes for inserting dates. "#" are mostly
unique to Access/Jet databases.

Yes, that's what I wanted. Now when you run that code and go to the
Immediate Window (which you get to using Ctrl-G), what's been printed

[quoted text clipped - 25 lines]

 

[Duane Hookom]

Sorry, no idea.

--
Duane Hookom
MS Access MVP

Hi Duane,

I have an ADP project maintaining attendance of employees. I made this
project on my home PC with Access 2002. This works fine. I transferred
this
project to my office PC with Access 2003. The pivot table created in
access
2002 does not seem to work. When I open a form in Pivot view, it gives an
error " The query could not be processed. The data provider didn't supply
any further error information."

I am not able to figure out this as the same pivot table view works on my
home pc with Access 2002.

Can you throw some light on this.

Appreciate your help.

Gmenon

The first posting didn't state this was an ADP. Considering that, the "#"
should be replaced by single quotes for inserting dates. "#" are mostly
unique to Access/Jet databases.

Yes, that's what I wanted. Now when you run that code and go to the
Immediate Window (which you get to using Ctrl-G), what's been printed

[quoted text clipped - 25 lines]

Gmenon

 

[gmenon100 via AccessMonster.com]

Thanks Duane.

Sorry, no idea.

Hi Duane,

[quoted text clipped - 25 lines]