DAO macro problem

[mani_singh]

ok guys i have a problem, ive input this vba code and when i run it, i get a "run time error 13 - type mismatch error" im running excel 97 and have setup the correct microsoft reference libraries!

the location for the .mdb and the table name is fine - i just want it to go to the test.mdb file and copy across the data from it into sheet one of the spreadsheet im in.


Sub GetTable()

' variables
Dim Db As Database
Dim Rs As Recordset
Dim Ws As Object
Dim i As Integer
Dim Path As String

Set Ws = Sheets("Sheet1")

Path = "h:\test.mdb"

Ws.Activate
Range("A1").Activate
Selection.CurrentRegion.Select
Selection.ClearContents
Range("A1").Select

'Set the Database, and RecordSet This Table exists in the database
Set Db = Workspaces(0).OpenDatabase(Path, ReadOnly:=True)

'This will set the RecordSet to all records in the Customers table
Set Rs = Db.OpenRecordset("kstable")



'This loop will collect the field names and place them in the first
'row starting at "A1"
For i = 0 To Rs.Fields.Count - 1
Ws.Cells(1, i + 1).Value = Rs.Fields(i).Name
Next i

'The next line simply formats the headers to bold font
Ws.Range(Ws.Cells(1, 1), Ws.Cells(1, Rs.Fields.Count)).Font.Bold = True

Ws.Range("A2").CopyFromRecordset Rs


Sheets("Sheet1").Select
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
Range("A1").Select

Rs.Close
Db.Close
End Sub


help please im kinda stuck here

 

[Richard Schollar]

Where does it error?

 

[mani_singh]

Set Rs = Db.OpenRecordset("kstable")

thank you for the quick responce

 

[mani_singh]

the data in the table is test data all in "text" charecter type

 

[mani_singh]

sorry i just cant work out why his is happening?! if you or anyon ehas any ideas please let me know as im stuck without this macro running !

thanks in advance

 

[Richard Schollar]

here you have:

Dim Db As Database 
Dim Rs As Recordset

in the code, try amending to:

Dim Db As DAO.Database 
Dim Rs As DAO.Recordset

 

[mani_singh]

thank you very much richard! all the best

FYI - you've help me a lot with these issues i've been having in excel - next time i'm in hampshire i'll buy you a drink.

thanks again!

mani

 

[Richard Schollar]

thank you very much richard! all the best

FYI - you've help me a lot with these issues i've been having in excel - next time i'm in hampshire i'll buy you a drink.

thanks again!

mani

To be honest, I wasn't 100% certain that would work - so it's good to know it did!