July 26, 2005 at 7:41 am
I am currently in the process of converting some Access functionality to T-SQL. What I need to do is connect to a FoxPro *.dbc table, manipulate that data and then export a SQL table to Excel.
Can anyone tell me if this is possible.
Thanks
July 26, 2005 at 8:38 am
Assuming I've understood your requirements correctly - yes, this is possible:
If this is a one-time thing using the wizards would be the quickest way.
1) create table in sql server with same structure as foxpro table
2) using import wizard in enterprise manager - import foxpro table into sql table
3) using export wizard in enterprise manager - export sql table into excel file
Otherwise you could create a DTS package - but you'd have to read up on it - BOL would be a good place to start - hth!
**ASCII stupid question, get a stupid ANSI !!!**
July 26, 2005 at 8:42 am
Not a one time job I'm afraid. Will be run daily after the conclusion of runnind a stored procedure. If possible was looking to append code to the end of the sp that would do the job.
I current have VB code that having generated report data, connects to our accounts *.dbc file via ADO for some info and uses that to manipulate the data further. The results table is then exported to an Excel table.
July 26, 2005 at 8:47 am
maybe if you explained the process some more.....???
eg: - what functionality are you converting to t-sql ? why do you have a foxpro table ? what is the sequence of events ? what are the ddls of tables involved ?
**ASCII stupid question, get a stupid ANSI !!!**
July 26, 2005 at 9:11 am
I have created a sp which populates a table to be the source of a report. The data is financial data based on products sold.
I wish to do the following within SQL every time this report is run.
1) For each record in my table I wish to obtain data from a FoxPro *.dbc file based upon values in that record.
2) These new values will be used to manipulate the existing data further.
3) The table then needs to be exported to Excel.
The VB code that facilitates this isbelow:
Sub OperaDefRevExport(PubYear As Long, PubMonth As Long)
Dim db As Database, DefRevRecs As Recordset
Dim BankingCo As String, CoLet As String
Dim OperaConn As New ADODB.Connection
Dim rsNomAc As New ADODB.Recordset
Dim rsSalesCode As New ADODB.Recordset
Dim SQLText As String
Dim NomAc As String
Dim CCntr As String
Dim NType As String
Dim NSubType As String
' Populate 'BankingCompany' and 'OperaSalesCode' fields
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE [Deferred Revenue Summary] INNER JOIN Titles ON [Deferred Revenue Summary].PubCode = Titles.PubCode SET [Deferred Revenue Summary].BankingCompany = [Titles].[BankingCompany], [Deferred Revenue Summary].OperaSalesCode = [Titles].[AcCode];"
DoCmd.SetWarnings True
'Add zero lines for publications excluding ceased publications to ensure previous cfwd, if present, is over-written with a zero
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO [Deferred Revenue Summary] ( PubCode, Title, BankingCompany, OperaSalesCode, [Recvd This Month], [VAT This Month], [Total This Month], [Income This Month], [Income cfwd], [Royalty on Recvd], [Royalty This Month], [Royalty cfwd] ) " & _
"SELECT Titles.PubCode, Titles.Title, Titles.BankingCompany, Titles.AcCode, 0 AS Expr1, 0 AS Expr2, 0 AS Expr3, 0 AS Expr4, 0 AS Expr5, 0 AS Expr6, 0 AS Expr7, 0 AS Expr8 " & _
"FROM [Deferred Revenue Summary] RIGHT JOIN Titles ON [Deferred Revenue Summary].PubCode = Titles.PubCode " & _
"WHERE ((([Deferred Revenue Summary].PubCode) Is Null) AND ((Titles.CeasedPublication)=False));"
DoCmd.SetWarnings True
'Remove ceased publication records where income cfwd = 0
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE DISTINCTROW [Deferred Revenue Summary].*, [Deferred Revenue Summary].[Income cfwd], Titles.CeasedPublication " & _
"FROM [Deferred Revenue Summary] INNER JOIN Titles ON [Deferred Revenue Summary].PubCode = Titles.PubCode " & _
"WHERE ((([Deferred Revenue Summary].[Income cfwd])=0) AND ((Titles.CeasedPublication)=True));"
DoCmd.SetWarnings True
Set db = DBEngine.Workspaces(0).Databases(0)
Set DefRevRecs = db.OpenRecordset("SELECT * FROM [Deferred Revenue Summary];", dbOpenDynaset)
If DefRevRecs.RecordCount = 0 Then
MsgBox "No records found", 48, "Deferred Revenue"
DefRevRecs.Close
db.Close
Exit Sub
End If
DefRevRecs.MoveFirst
Do Until DefRevRecs.EOF
BankingCo = DefRevRecs("BankingCompany")
Select Case BankingCo
Case "NPUB"
CoLet = "N"
Case "NTCR"
CoLet = "R"
Case "WARC"
CoLet = "W"
Case Else
CoLet = ""
End Select
If CoLet <> "" Then
OperaConn.ConnectionString = "Driver=Microsoft Visual Foxpro Driver;UID=;" & _
"SourceType=DBC;SourceDB=i:\opera\data\comp_" & CoLet & ".dbc"
'Debug.Print OperaConn.ConnectionString
OperaConn.Open
SQLText = "SELECT ssale.ss_nominal FROM ssale WHERE ss_acode='" & DefRevRecs("OperaSalesCode") & "';"
rsSalesCode.Open SQLText, OperaConn, adOpenStatic
If Not rsSalesCode.EOF Then
NomAc = Trim(Left(rsSalesCode("ss_nominal"), 8))
CCntr = Trim(Mid(rsSalesCode("ss_nominal"), 9))
rsSalesCode.Close
SQLText = "SELECT na_type, na_subt FROM nacnt WHERE na_acnt='" & NomAc & "' AND na_cntr='" & CCntr & "'"
rsNomAc.Open SQLText, OperaConn, adOpenStatic
If Not rsNomAc.EOF Then
NType = rsNomAc("na_type")
NSubType = rsNomAc("na_subt")
End If
rsNomAc.Close
Else
NomAc = ""
CCntr = ""
NType = ""
NSubType = ""
rsSalesCode.Close
End If
OperaConn.Close
DefRevRecs.Edit
If CCntr <> "" Then
DefRevRecs("OperaCntr") = CCntr
End If
If NType <> "" Then
DefRevRecs("OperaType") = NType
End If
If NSubType <> "" Then
DefRevRecs("OperaSubType") = NSubType
End If
DefRevRecs.Update
End If
DefRevRecs.MoveNext
Loop
DefRevRecs.Close
db.Close
If Dir("i:\operaii\subs" & Right(CStr(PubYear * 100 + PubMonth), 4) & ".xls") <> "" Then
Kill "i:\operaii\subs" & Right(CStr(PubYear * 100 + PubMonth), 4) & ".xls"
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "DefRevExport", "i:\opera\subs" & Right(CStr(PubYear * 100 + PubMonth), 4) & ".xls", True
MsgBox "Deferred Revenue data exported to:" & Chr(13) & "i:\operaii\subs" & Right(CStr(PubYear * 100 + PubMonth), 4) & ".xls", 48, "Deferred Revenue"
End Sub
July 26, 2005 at 10:02 am
pete - your best bet seems to be creating and running a scheduled dts package -
one way of doing this : you could append half of what you're doing in your existing procedure...all your inserts, deletes, updates etc... and then use dts for the remaining import from foxpro and export to excel task...
you could use ActiveX scripts...
again - BOL would be your source to get started...if you need any help with the t-sql behind modifying your sql server data then pl. post the ddls and the modification requirements...
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply