Access -----> SQL

[varean]

We moved a database from ms access 2003 to SQL server 2000. We kept all queries in ms access and link to the tables via ODBC. Can someone please tell me what is wrong with this code, I'm getting the error " problems obtaining data" when it tries to import the data into excel.
it's supposed to import data to an excel spreadsheet.

Private Sub cmdcaseload_Click()
On Error Resume Next
DoCmd.DeleteObject acTable, "FirstTimeClients"
DoCmd.OpenQuery "clients_first_time_Table"
DoCmd.DeleteObject acTable, "Clients"
DoCmd.DeleteObject acTable, "caseload"
DoCmd.DeleteObject acTable, "ftc"
If IsNull(d3) Then
DoCmd.OpenQuery "ftc_Table"
DoCmd.OpenQuery "clients_Table"
DoCmd.OpenQuery "outcome_caseload_table"
Else
DoCmd.OpenQuery "ftc_Table_after"
DoCmd.OpenQuery "clients_Table_after"
DoCmd.OpenQuery "outcome_caseload_table_after"
End If
DoCmd.TransferDatabase acExport, "Microsoft Access", "c:\work\drb.mdb", acTable, "ftc", "ftc", False
DoCmd.TransferDatabase acExport, "Microsoft Access", "c:\work\drb.mdb", acTable, "firsttimeclients", "firsttimeclients", False
DoCmd.TransferDatabase acExport, "Microsoft Access", "c:\work\drb.mdb", acTable, "clients", "clients", False
DoCmd.TransferDatabase acExport, "Microsoft Access", "c:\work\drb.mdb", acTable, "caseload", "caseload", False
Call Shell("EXCEL.EXE c:\work\satp\caseload.xls", 1)

 

[dinger2121]

If you moved the data to SQL Server, then what are your queries using as the table to query from? You may have to try creating link tables to your sql server.

 

[AlexCuse]

Do the queries you are trying to run do so when manually opened? There are a lot of places the code you posted could be failing. Please try to narrow it down a bit

[small]----signature below----[/small]
Now you can go where the people are one!
Now you can go where they get things done!

 

[varean]

thanks for your replies. I do have an odbc connection to the tables. I believe the problem is with the pivot table in excel, it gets the data from c:\work\drb.mdb I manually run the queries and they all run fine. The data source did not change.

 

[varean]

This is the first error that comes up folks:
[microsoft][odbc microsoft access driver] too few parameters.Expected 1.

 

[Remou]

For the most part, this error means that a variable has not got a value.

 

[jrbarnett]

if you're getting

[microsoft][odbc microsoft access driver]

in your error message, then you're using the Access driver to connect to SQL Server (I'm not sure how, unless you're using DAO to connect, which isn't advisable).
Please re create your data source using the SQL Server ODBC driver and try again.

John