[AccessD] Grab Filenames

Your problem lies in your connection string...
"ODBC;DSN=db_pd1;UID=testuser;PWD=tu1234;DATABASE=CIRC"

DSN "db_pd1" exists on your machine but probably doesn't exist on the user
machine.  In order for this to work, you will need to create the DSN on the
user machine.  You can have your code quietly check for the presence of the
DSN and create it if it doesn't exist without the user having to do
anything.

Doris Manning
Database Administrator
Hargrove Inc.
www.hargroveinc.com


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Ron Allen
Sent: Wednesday, May 21, 2003 1:06 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Grab Filenames


Access97 and SQL Server2000. The idea is to access a table 
in a SQL Server20000 database to populate a local table in 
an Access97 front-end without using a stored passthrough 
query or otherwise exposing the data or security 
information to the user. The user will only have a 
compiled .mde file, so this code would be inaccessible.

The below code works great on my development machine, but 
when I try it on any of three of my user's machines I get 
Library or Project not found. I can, however, create 
stored passthrough queries on those machines, and all of 
them have all options installed in Access97. My 
development machine is WinXP Pro, user machines are 
typically Win98. Any suggestions?

Thanks,

Ron




Public Sub TestTempPassthrough()
On Error GoTo ErrorHandle

Dim dbDW As Database
Dim dbJet As Database
Dim qdfDW As QueryDef
Dim rstDW As Recordset
Dim rstJet As Recordset
Dim strSQL As String
Dim strCurDate As String

     DoCmd.SetWarnings False
     strCurDate = Format(Date, "mm/dd/yyyy")
     
     strSQL = "DELETE OutletList.* FROM OutletList;"
     DoCmd.RunSQL (strSQL)
     
     strSQL = "SELECT rte as Route, out_nbr as Outlet, 
vnd_nbr as Vendor, "
     strSQL = strSQL & "dist as District, dept_code as 
Dept, area_code as Area, "
     strSQL = strSQL & "zone_code as Zone, pd_owned as 
PDOwned, pd_billed as PDBilled, "
     strSQL = strSQL & "stld_delivered as STLDelivered, 
contract as Contract, "
     strSQL = strSQL & "abc_dlv_code1 as ABCDlvCode1, 
abc_dlv_code2 as ABCDlvCode2, "
     strSQL = strSQL & "abc_paid As ABCPaid, eff_date As 
EffDate, end_date As EndDate "
     strSQL = strSQL & "FROM circ.dbo.Distribution "
     strSQL = strSQL & "WHERE (eff_date <= '" & strCurDate 
& "' AND end_date > '" & strCurDate & "') "
     strSQL = strSQL & "AND (rte <> '' and rte is not null 
and rte <> '9999') "
     strSQL = strSQL & "AND (out_nbr <> 0 and out_nbr is 
not null) "
     strSQL = strSQL & "AND (dept_code <> 'GC' and 
dept_code <> 'TM')"

     Set dbDW = DBEngine.Workspaces(0).Databases(0)
     Set qdfDW = dbDW.CreateQueryDef("")
     qdfDW.Connect = 
"ODBC;DSN=db_pd1;UID=testuser;PWD=tu1234;DATABASE=CIRC"
     qdfDW.SQL = strSQL
     qdfDW.ReturnsRecords = True
     qdfDW.ODBCTimeout = 0
     Set rstDW = qdfDW.OpenRecordset()

     Set dbJet = CurrentDb()
     Set rstJet = dbJet.OpenRecordset("OutletList", 
dbOpenTable)
     
With rstDW
     .MoveFirst
     Do
         rstJet.AddNew
         rstJet![AsOfDate] = Date
         rstJet![Route] = CInt(![Route])
         rstJet![Outlet] = ![Outlet]
         rstJet![Vendor] = ![Vendor]
         rstJet![District] = ![District]
         rstJet![Dept] = ![Dept]
         rstJet![Area] = ![Area]
         rstJet![Zone] = ![Zone]
         rstJet![PDOwned] = ![PDOwned]
         rstJet![PDBilled] = ![PDBilled]
         rstJet![STLDelivered] = ![STLDelivered]
         rstJet![Contract] = ![Contract]
         rstJet![ABCDlvCode1] = ![ABCDlvCode1]
         rstJet![ABCDlvCode2] = ![ABCDlvCode2]
         rstJet![ABCPaid] = ![ABCPaid]
         rstJet.Update
         .MoveNext
     Loop Until .EOF
         
End With

ExitSub:
     Set rstDW = Nothing
     Set qdfDW = Nothing
     Set dbDW = Nothing
     Set rstJet = Nothing
     Set dbJet = Nothing
     DoCmd.SetWarnings True
     Exit Sub

ErrorHandle:
     MsgBox Err.Number & " " & Err.Description & " in 
TestTempPassthrough"
     Resume ExitSub
     
End Sub
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com