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:
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,279
Messages
5,836,367
Members
430,424
Latest member
ALHK022

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