VBA - Can't Find DataSource - worked until upgraded to w2k

[Jado]

Hi

i've created an automated process in Access 97 that creates an excel sheet
from data in an access table.

the whole process worked fine...

until i upgraded to Windows 2000 Pro

now each time it runs i get an 'Open Data Source' Message.

anyone know why??

Thanks

Jado

 

[Francine Kubaka]

Did you use a recorded macro? You may need to re-record it....

Will this help? --
http://www.mvps.org/word/FAQs/MailMerge/CreateADataSource.htm

Cheers,

FK

 

[Jado]

Hi

i've got some more info...

when the open data source message appears...

if i select the Microsoft Access Driver from the list... then click ok

it asks for a db user name and password.....

if i enter user: Admin Password: leave blank

it works fine!

any idea's

Thanks

Jado

 

[Francine Kubaka]

Jado,
Sorry, it's near impossible to help remedy your problem without knowing how
you created your 'automated procedure' in the first place. If you simply
recorded it, it would be easier to re-record. If you coded it yourserlf in
VBE, then please supply more details - what kind of connection you used etc
etc.

From what I can tell, you previous procedure used an ODBC connection. Then,
after the installation of teh new OS, the DSN file was lost or corrupted. If
I am correct about the type of connection, you may need to re-build the DSN,
but I can be very, very wrong.

Cheers,
FK

 

[Jado]

Hi Sorry for the confusion....

here's the code..

----------------------------------------------
Function LeadsToExcel(dbPath As String, BasePath As String, qryName As
String, docName As String) '

Dim ojXls As Excel.Application
Dim xlDoc As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim mySQL As String
Dim myCon As String

On Error GoTo Err_Reset

Set ojXls = New Excel.Application
Set xlDoc = ojXls.Workbooks.Add
Set xlSheet = xlDoc.Worksheets.Item(1)


With xlSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access 97 Database;DBQ=" & dbPath & ";" _
), Array( _
"DefaultDir=\\Server\Path;" _
), Array("DriverId=281;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;")), _
Destination:=xlSheet.Range("A1"))
.SQL = Array( _
"SELECT * " & Chr(13) & "" & Chr(10) & "FROM `" & qryName & "` `" &
qryName & "`")
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = False
.Delete
End With
On Error Resume Next
Kill BasePath & "\Path\" & docName & ".xls"
ChDir BasePath & "\Path\"
ojXls.ActiveWorkbook.SaveAs FileName:=BasePath & "\Path\" & docName &
".xls", FileFormat:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, CreateBackup:=False

ChDir "\\Server\Path"
ojXls.ActiveWorkbook.SaveAs FileName:="Server\Path\" & docName & ".xls",
FileFormat:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, CreateBackup:=False

xlDoc.Close
ojXls.Quit

Set xlSheet = Nothing
Set xlDoc = Nothing
Set ojXls = Nothing
Exit Function

Err_Reset:

Set xlSheet = Nothing
Set xlDoc = Nothing
Set ojXls = Nothing
MsgBox "Excel Error: " & Err.Number & " - " & Err.Description
End Function
----------------------------------------------------------------------------
--

any ideas..?

Thanks

Jado