Troubled Tribble needs SQL/VBA help

Get help on databases - MySQL, Oracle, Access, etc.
User avatar
Tribble
Registered User
Posts: 88456
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

Post by Tribble »

I am checking that now. Turns out none of the programs have been updated - ever.
Image
User avatar
Tribble
Registered User
Posts: 88456
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

Post by Tribble »

*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.

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;
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.

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];
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
Image
User avatar
Tribble
Registered User
Posts: 88456
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

Post by Tribble »

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)
Image
User avatar
hamin_aus
Forum Moderator
Posts: 18358
Joined: 28 Aug 2003, 02:00
Processor: Intel i7 3770K
Motherboard: GA-Z77X-UP4 TH
Graphics card: EVGA GTX680 SC
Memory: 32GB G.Skill Ripjaws
Location: Where beer does flow and men chunder
Contact:

Re: Update - New SQL problems

Post by hamin_aus »

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 :D
But otherwise have a look, you might find it worthwhile:
http://decipherinfosys.wordpress.com/20 ... a-package/
Image
User avatar
Tribble
Registered User
Posts: 88456
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

Post by Tribble »

:hug: 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 :D
Image
User avatar
hamin_aus
Forum Moderator
Posts: 18358
Joined: 28 Aug 2003, 02:00
Processor: Intel i7 3770K
Motherboard: GA-Z77X-UP4 TH
Graphics card: EVGA GTX680 SC
Memory: 32GB G.Skill Ripjaws
Location: Where beer does flow and men chunder
Contact:

Re: Update - New SQL problems

Post by hamin_aus »

Glad to be of some help :D
Image
User avatar
Tribble
Registered User
Posts: 88456
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

Post by Tribble »

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.
Image
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: Dumb Tribble needs SQL/VBA help

Post by RuadRauFlessa »

Let me do some research....
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
User avatar
Tribble
Registered User
Posts: 88456
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

Post by Tribble »

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
Image
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: Dumb Tribble needs SQL/VBA help

Post by RuadRauFlessa »

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

Code: Select all

Option Compare Database

Private Sub Form_Timer()

End Sub
What you need now is to fill in the blanks. I have taken the liberty of doing an example for your purposes.

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
all you need to do is to replace the query

Code: Select all

LSQL = "select * from contacts"
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.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
User avatar
hamin_aus
Forum Moderator
Posts: 18358
Joined: 28 Aug 2003, 02:00
Processor: Intel i7 3770K
Motherboard: GA-Z77X-UP4 TH
Graphics card: EVGA GTX680 SC
Memory: 32GB G.Skill Ripjaws
Location: Where beer does flow and men chunder
Contact:

Re: Troubled Tribble needs SQL/VBA help

Post by hamin_aus »

Trigger to lauch the alert form when a new line is added to the message table?
Image
User avatar
Tribble
Registered User
Posts: 88456
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

Post by Tribble »

Oh that looks fantastic - I will log in tonight and give it a try.

Thanks RRF :hug:
Image
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: Troubled Tribble needs SQL/VBA help

Post by RuadRauFlessa »

Let me know how it goes.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
User avatar
Tribble
Registered User
Posts: 88456
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

Post by Tribble »

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.
Image
User avatar
hamin_aus
Forum Moderator
Posts: 18358
Joined: 28 Aug 2003, 02:00
Processor: Intel i7 3770K
Motherboard: GA-Z77X-UP4 TH
Graphics card: EVGA GTX680 SC
Memory: 32GB G.Skill Ripjaws
Location: Where beer does flow and men chunder
Contact:

Re: Troubled Tribble needs SQL/VBA help

Post by hamin_aus »

Profit sharing?
Image
User avatar
Tribble
Registered User
Posts: 88456
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

Post by Tribble »

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.
Image
User avatar
Tribble
Registered User
Posts: 88456
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

Post by Tribble »

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

Code: Select all

 sConnectionString = "ODBC;DSN=dsnCallCentreControl2;Driver={SQL Server Native Client 10.0};SERVER=I-TALKCRM;DATABASE=CallCentreControl;UID=Datacapture;PWD=capture;"
And this is the Private Sub

Code: Select all

Private Sub Validate()
     DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
     lblValidate.Caption = ValidateRecord(RecID)
End Sub
And this is how we call the stored procedure

Code: Select all

Private Sub tabMain_Change()
    Validate
End Sub
I will post the validation code if it will help
Image
User avatar
Tribble
Registered User
Posts: 88456
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

Post by Tribble »

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

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

On the Competitor Contracted sheet I have

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

And similar code on the Sales Report sheet.
Image
User avatar
Tribble
Registered User
Posts: 88456
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

Post by Tribble »

I still need help with this please
Image
Post Reply