Troubled Tribble needs SQL/VBA help
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Linking to SQL 2008 R2
I am checking that now. Turns out none of the programs have been updated - ever.
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Linking to SQL 2008 R2
*Sigh* Never mind - I see my mistake now - bracket is missing[/size]
I tried (with RRF's assistance) to make an SQL query. I get an error. Just note - I have never written an SQL query before.
I have deleted more than 4/5ths of the query.
This is the error
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'FROM'.
And yes - I have repeated the whole thing with [dbo].[tblMain] and still get errors.
Error Msg:
Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'FROM'.
What I am trying to do is combine all the fields into one long string and export it as a txt file, with each record on a separate line. I have used Char as the type and specified the length of each field. Each field must remain the correct size.
eg:
CardType char(2) Checked
ApplicNo char(20) Checked
SBSAResIBT char(8) Checked
UserIdCode char(7) Checked
CampaignCode char(6) Checked
I tried (with RRF's assistance) to make an SQL query. I get an error. Just note - I have never written an SQL query before.
Code: Select all
SELECT [tblMain].[CardType]+ Replicate(' ',2-[tblMain].[CardType]+
[tblMain].[ApplicNo]+
[tblMain].[SBSAResIBT]+
[tblMain].[UserIdCode]+ Replicate(' ',7-[tblMain].[UserIdCode]+
[tblMain].[CampaignCode]+
[tblMain].[CPScorporationID]+
[tblMain].[NCAProductCode]+ Replicate(' ',3-[tblMain].[NCAProductCode]+
[tblMain].[NCASubProductCode]+ Replicate(' ',3-[tblMain].[NCASubProductCode]+
[tblMain].[CardNumber]+
[tblMain].[ApplicSeqNo]+
[tblMain].[SBSAResFiller]+
[tblMain].[ChannelID]+
[tblMain].[SBSAResFiller2]
FROM tblMain;
This is the error
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'FROM'.
And yes - I have repeated the whole thing with [dbo].[tblMain] and still get errors.
Code: Select all
SELECT [dbo].[tblMain].[CardType]+ Replicate(' ',2-[dbo].[tblMain].[CardType]+
[dbo].[tblMain].[ApplicNo]+
[dbo].[tblMain].[SBSAResIBT]+
[dbo].[tblMain].[UserIdCode]+ Replicate(' ',7-[dbo].[tblMain].[UserIdCode]+
[dbo].[tblMain].[CampaignCode]+
[dbo].[tblMain].[CPScorporationID]+
[dbo].[tblMain].[NCAProductCode]+ Replicate(' ',3-[dbo].[tblMain].[NCAProductCode]+
[dbo].[tblMain].[NCASubProductCode]+ Replicate(' ',3-[dbo].[tblMain].[NCASubProductCode]+
[dbo].[tblMain].[CardNumber]+
[dbo].[tblMain].[ApplicSeqNo]+
[dbo].[tblMain].[SBSAResFiller]+
[dbo].[tblMain].[ChannelID]+
[dbo].[tblMain].[SBSAResFiller2]
FROM [dbo].[tblMain];
Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'FROM'.
What I am trying to do is combine all the fields into one long string and export it as a txt file, with each record on a separate line. I have used Char as the type and specified the length of each field. Each field must remain the correct size.
eg:
CardType char(2) Checked
ApplicNo char(20) Checked
SBSAResIBT char(8) Checked
UserIdCode char(7) Checked
CampaignCode char(6) Checked
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Update - New SQL problems
How do I made the query give me a text file? I have tried everything - To text and To File and neither produces a .txt file anywhere on my drive (let alone where I asked it to go)
- hamin_aus
- Forum Moderator
- Posts: 18363
- Joined: 28 Aug 2003, 02:00
- Processor: Intel i7 3770K
- Motherboard: GA-Z77X-UP4 TH
- Graphics card: Galax GTX1080
- Memory: 32GB G.Skill Ripjaws
- Location: Where beer does flow and men chunder
- Contact:
Re: Update - New SQL problems
Using xp_cmdshell, which needs a little tweaking of the backend to enable it, and then the BCP command in the script
Here's a fairly comprehensive article on enabling xp_cmdshell and the BCP syntax
http://mangalpardeshi.blogspot.com/2008 ... -file.html
There's also, SSIS - which can be tricky if you have never worked with it before.
Don't bother with this if you are running SQL Express, as can't like to SSIS
But otherwise have a look, you might find it worthwhile:
http://decipherinfosys.wordpress.com/20 ... a-package/
Here's a fairly comprehensive article on enabling xp_cmdshell and the BCP syntax
http://mangalpardeshi.blogspot.com/2008 ... -file.html
There's also, SSIS - which can be tricky if you have never worked with it before.
Don't bother with this if you are running SQL Express, as can't like to SSIS
But otherwise have a look, you might find it worthwhile:
http://decipherinfosys.wordpress.com/20 ... a-package/
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Update - New SQL problems
Thank you so much. I didn't use your method but you showed me what I was bot doing. My version works now and the text file is beautiful. Thank you Jamin and RRF for all your help
- hamin_aus
- Forum Moderator
- Posts: 18363
- Joined: 28 Aug 2003, 02:00
- Processor: Intel i7 3770K
- Motherboard: GA-Z77X-UP4 TH
- Graphics card: Galax GTX1080
- Memory: 32GB G.Skill Ripjaws
- Location: Where beer does flow and men chunder
- Contact:
Re: Update - New SQL problems
Glad to be of some help
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Dumb Tribble needs SQL/VBA help
Oh you were - absolutely.
Now for the next thing......please......<tries to bat eyelashes>
I have created a very simple messaging system in my database. How do I use either SQL or VBA to write a Public Function (I think that is the right one) that will make a message pop up telling them they have a new message - regardless of where they are in the program?
I have managed to do it on a form - but it requires that the form be requeried / refreshed before the pop up will pop up. I want this to be independent of what they are doing.
Similarly - I need a pop up to appear when it is time to make the next scheduled call - or 15 mins before it.
Hope I stated my situation clearly enough - if not - say so and I will try again.
/Honesty : I do not have to do these things - I would like to do them. They are not critical, but I would really appreciate your help.
Now for the next thing......please......<tries to bat eyelashes>
I have created a very simple messaging system in my database. How do I use either SQL or VBA to write a Public Function (I think that is the right one) that will make a message pop up telling them they have a new message - regardless of where they are in the program?
I have managed to do it on a form - but it requires that the form be requeried / refreshed before the pop up will pop up. I want this to be independent of what they are doing.
Similarly - I need a pop up to appear when it is time to make the next scheduled call - or 15 mins before it.
Hope I stated my situation clearly enough - if not - say so and I will try again.
/Honesty : I do not have to do these things - I would like to do them. They are not critical, but I would really appreciate your help.
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Dumb Tribble needs SQL/VBA help
Thank you - I have tried to look around but I don't actually know how to word my query so I don't quite get what I want
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: Dumb Tribble needs SQL/VBA help
OK... I dunno what you currently have running for the message system but I would presume you have a table holding the messages. Working on that assumption I would enable the timer on each form by giving the Timer_Interval property on the form a value. That being done you have to specify a function to execute when the OnTimer event fires. Easy way to do this is to click on the "..." next to the event in the Property Sheet under events. Note that this is all in the Design view of a form. When you click on the "..." it will open up the VB code and add a function for you that will fire when the timer runs out.
If you have no functions on the form it will look something like
What you need now is to fill in the blanks. I have taken the liberty of doing an example for your purposes.
all you need to do is to replace the query
with whatever query you need to return the messages for you. You can then also change the call to MsgBox with whatever you need displayed.
If you have no functions on the form it will look something like
Code: Select all
Option Compare Database
Private Sub Form_Timer()
End Sub
Code: Select all
Option Compare Database
Private Sub Form_Timer()
'do a query to check if we have notifications
Dim db As Database
Dim Lrs As DAO.Recordset
Dim LSQL As String
Dim LGST As String
'Open connection to current Access database
Set db = CurrentDb()
'Create SQL statement to retrieve value from Message table
LSQL = "select * from contacts"
Set Lrs = db.OpenRecordset(LSQL)
'display the notification
If (Lrs.RecordCount > 0) Then
'If there are notifications to display...
MsgBox "You have " & Lrs.RecordCount & " Notifications", vbInformation, "System Messages"
Else
'If there are no notifications to display.... You can comment this out if you want to
MsgBox "No Current Messages", vbInformation, "System Messages"
End If
'Close down the query
Lrs.Close
Set Lrs = Nothing
End Sub
Code: Select all
LSQL = "select * from contacts"
- Spoiler (show)
- hamin_aus
- Forum Moderator
- Posts: 18363
- Joined: 28 Aug 2003, 02:00
- Processor: Intel i7 3770K
- Motherboard: GA-Z77X-UP4 TH
- Graphics card: Galax GTX1080
- Memory: 32GB G.Skill Ripjaws
- Location: Where beer does flow and men chunder
- Contact:
Re: Troubled Tribble needs SQL/VBA help
Trigger to lauch the alert form when a new line is added to the message table?
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Troubled Tribble needs SQL/VBA help
Oh that looks fantastic - I will log in tonight and give it a try.
Thanks RRF
Thanks RRF
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Troubled Tribble needs SQL/VBA help
I have to tell some one. Any one -= preferably someone who cares.
The bank audited my CRM program today and it passed! They have approved it and signed it off. I am sooo over the moon today. Thanks RRF and Jamin for all your help. And all the others who helped too. How I got a SQL CRM program through their process escapes me but I did. Shows you that you don't actually have to know what you are doing in order to do it Now I have 3 more to build. RRF - you will be hearing from me when I get back from PE.
The bank audited my CRM program today and it passed! They have approved it and signed it off. I am sooo over the moon today. Thanks RRF and Jamin for all your help. And all the others who helped too. How I got a SQL CRM program through their process escapes me but I did. Shows you that you don't actually have to know what you are doing in order to do it Now I have 3 more to build. RRF - you will be hearing from me when I get back from PE.
- hamin_aus
- Forum Moderator
- Posts: 18363
- Joined: 28 Aug 2003, 02:00
- Processor: Intel i7 3770K
- Motherboard: GA-Z77X-UP4 TH
- Graphics card: Galax GTX1080
- Memory: 32GB G.Skill Ripjaws
- Location: Where beer does flow and men chunder
- Contact:
Re: Troubled Tribble needs SQL/VBA help
Profit sharing?
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Troubled Tribble needs SQL/VBA help
Happy to treat you to dinner when I get paid Sadly these are not big programs so the profit is not that great. But great exposure and experience.
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Troubled Tribble needs SQL/VBA help
Ok - the CRM has been running well for a while now. Last week we started experiencing ODBC connection issues and now the verification code in the program is causing problems. I can only assume that I changed something vital and not having written the code, I have no idea what it could be. I have looked through it but cannot see anything that could cause these problems.
Could someone tell me what sort of things could cause the connection issues so that I can begin to eliminate them.
This is the connection string I am using
And this is the Private Sub
And this is how we call the stored procedure
I will post the validation code if it will help
Could someone tell me what sort of things could cause the connection issues so that I can begin to eliminate them.
This is the connection string I am using
Code: Select all
sConnectionString = "ODBC;DSN=dsnCallCentreControl2;Driver={SQL Server Native Client 10.0};SERVER=I-TALKCRM;DATABASE=CallCentreControl;UID=Datacapture;PWD=capture;"
Code: Select all
Private Sub Validate()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
lblValidate.Caption = ValidateRecord(RecID)
End Sub
Code: Select all
Private Sub tabMain_Change()
Validate
End Sub
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Troubled Tribble needs SQL/VBA help
New Problem
I need to add code to a spreadsheet (yes Excel) but it must run on 2003. Trick is I am working on 2010.
So I have this code from the original document and need to adjust it to fit the new layout. Problem is I very rarely work on someone else's code and perhaps because it is Sunday and I have been working all weekend, but I cannot see what I am doing.
1. It has a contract expiry date (column V (11)
2. If it gets close to the expiry date then it must move to another sheet so the contract can be renewed.
3. A reminder needs to pop up telling the person the contract is due for renewal based on column 11.
4. It then needs to move the record to another sheet
5. It needs to delete from the original sheet.
An you see if this looks correct please
On the Competitor Contracted sheet I have
And similar code on the Sales Report sheet.
I need to add code to a spreadsheet (yes Excel) but it must run on 2003. Trick is I am working on 2010.
So I have this code from the original document and need to adjust it to fit the new layout. Problem is I very rarely work on someone else's code and perhaps because it is Sunday and I have been working all weekend, but I cannot see what I am doing.
1. It has a contract expiry date (column V (11)
2. If it gets close to the expiry date then it must move to another sheet so the contract can be renewed.
3. A reminder needs to pop up telling the person the contract is due for renewal based on column 11.
4. It then needs to move the record to another sheet
5. It needs to delete from the original sheet.
An you see if this looks correct please
Code: Select all
Option Explicit
Sub CheckDates()
Application.EnableEvents = False
Dim i As Integer
Dim j As Integer
Dim LR As Integer
i = 0: j = 0
Worksheets("Competitor Contracted").Activate
Do While Not IsEmpty(Cells(i + 4, 11))
Cells(i + 4, 11).Select
If DateDiff("d", Now(), Cells(i + 4, 11).Value) <= 390 Then
j = j + 1
End If
i = i + 1
Loop
If j > 0 Then
Call shoDateCheckerForm
Else
MsgBox "There are currently no leads approaching contract renewal date.", vbInformation
End If
Application.EnableEvents = True
End Sub
-----------------------------------------------
Sub CheckDatesAndCopy()
Application.EnableEvents = False
On Error Resume Next
Application.ScreenUpdating = False
Dim i As Integer, j As Integer
Dim LR As Integer
i = 0: j = 0
Worksheets("Competitor Contracted").Activate
Do While Not IsEmpty(Cells(i + 4, 11))
Cells(i + 4, 11).Select
If DateDiff("d", Now(), Cells(i + 4, 11).Value) <= 390 Then
j = j + 1
Worksheets("Competitor Contracted").Activate
Call setWorkingCell
Worksheets("Competitor Contracted").Activate
LR = Cells(i + 4, 11).Row
Range("a" & LR, "AD" & LR).Select
Range("a" & LR, "AD" & LR).Copy Worksheets("Sales Report").Range(WorkingOpenCell.Address)
End If
i = i + 1
Loop
Application.EnableEvents = False
MsgBox "All leads that are approaching contract" & vbCrLf & vbCrLf & "renewal date have now been moved to" & vbCrLf & vbCrLf & " your Sales Report sheet to pursue."
Call CheckDatesAndDelete
End Sub
---------------------------------------------
Sub CheckDatesAndDelete()
Application.ScreenUpdating = False
Dim i As Integer, j As Integer
Dim LR As Integer
i = 0
Worksheets("Competitor Contracted").Activate
Do While Not IsEmpty(Cells(i + 4, 11))
Cells(i + 4, 11).Select
If DateDiff("d", Now(), Cells(i + 4, 11).Value) <= 390 Then
LR = Cells(i + 4, 11).Row
Range("a" & LR, "AD" & LR).Delete Shift:=xlUp
i = 0
Else
i = i + 1
End If
Loop
End Sub
--------------------------------------------------------
Sub shoDateCheckerForm()
UserForm1.Show
End Sub
Code: Select all
Private Sub setWorkingCell()
Worksheets("Competitor Contracted").Activate
a1 = 4: b1 = 1
Do While Not IsEmpty(Cells(a1, b1))
a1 = a1 + 1
Loop
Set WorkingOpenCell = Cells(a1, b1)
Cells(a1, b1).Select
Worksheets("Competitor Contracted").Activate
End Sub
- Tribble
- Registered User
- Posts: 88465
- Joined: 08 Feb 2007, 02:00
- Processor: Intel Core i7-4770K CPU@3.50GHz
- Motherboard: ACPI x64-based PC
- Graphics card: GeForce GTX 780 Ti
- Memory: 16GB
- Location: Not here
- Contact:
Re: Troubled Tribble needs SQL/VBA help
I still need help with this please