DAO macro problem

mani_singh

Well-known Member
Joined
Jul 24, 2007
Messages
583
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 :confused:
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

mani_singh

Well-known Member
Joined
Jul 24, 2007
Messages
583

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
here you have:

Code:
Dim Db As Database 
Dim Rs As Recordset

in the code, try amending to:

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

mani_singh

Well-known Member
Joined
Jul 24, 2007
Messages
583
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

:biggrin::biggrin:

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

Watch MrExcel Video

Forum statistics

Threads
1,122,511
Messages
5,596,581
Members
414,079
Latest member
Frills

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top