Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Run-Time Error '13': Type Mismatch

Status
Not open for further replies.
darkhat01

darkhat01

IS-IT--Management
Apr 13, 2006
144
0
0
US
I am getting a Run-Time Error '13': Type Mismatch. The Set db_data = NewConnection is what is highlighted in yellow. Would anyone know why this is happening? I got the code from: I am trying to learn how to import a csv file into a access table. Also is the DSN the location of where the csv files should be???

Code: 
Private Sub ImportData_Click()


    MsgBox "The ID being used is: " & intID 'Error Check
    MsgBox "Using dbFile: " & dbFile 'Error Check
    
    Set db_data = NewConnection
    db_data.CursorLocation = adUseClient

    'Set up DSNless connection
    db_data.Open "PROVIDER=MSDASQL;dsn=C:\MyDB Files;uid=;pwd=;database=;"

    Set Ado_data = NewRecordset
    
    'Note exactly the same select statement as would be used in a relational database

    Ado_data.Open "select name, salary from employee.csv e, salary.csv s " & _
    "where e.id=s.id", adOpenStatic, adLockOptimistic

    If Ado_data.RecordCount < 1 Then
        MsgBox "No data found"
        Exit Sub
    End If

    'Go to the first record in the set and loop around till no more available

    Ado_data.MoveFirst
    For i = 0 To Ado_data.RecordCount - 1
        MsgBox (Ado_data.Fields.Item(0).Value & " " & Ado_data.Fields.Item(1).Value)
        Ado_data.MoveNext
    Next

    Ado_data.Close
    db_data.Close


End Sub

Thanks,

Darkhat01
 
Sort by date Sort by votes
You need to DIM your variable as the appropriate object eg. DIM db_Data as ADODB.Connection

To get the appropriate options there, you will need to make sure that you have the appropriate references set - that will depend on the type of connection you are using and the version of office.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
The DSN is the name you gave to the text driver you should have set up - its not the name of a file or directory.

If you don't specify the path to the text files in your select statement it will assume they are in the directory from which you run the program.

Aslo, importantly, you have not copied down the code in the FAQ correctly

It should be

Set db_data = New Connection NOT
Set db_data = NewConnection

 
Upvote 0 Downvote
I made the changes that both of you suggested. But I am getting an error that still highlights New Connection. I also Made the DIM change.

Why would this be....

Maybe I should use a FileSystemObject... Any ideas on this? Does anyone know of a tutorial for using FileSystemObject and doing a text stream out on the web?
 
Upvote 0 Downvote
please post what you have changed your code to

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
Sorry the new code is:

Code: 
Private Sub ImportData_Click() 
Dim intID As Integer 'ID from manager record
Dim dbFile As String 'Location of our Access database file
Dim sSQL As String 'Our SQL query
Dim db_Data As ADODB.Connection

dbFile = Me!txtFileLocation
intID = Me!ID

 MsgBox "The ID being used is: " & intID 'Error Check
 MsgBox "Using dbFile: " & dbFile 'Error Check
    
    Set db_Data = New Connection
    db_Data.CursorLocation = adUseClient

    'Set up DSNless connection
    db_Data.Open "PROVIDER=MSDASQL;dsn=C:\Documents and Settings\treptor\Desktop\RaysDB;uid=;pwd=;database=;"

    Set Ado_data = NewRecordset
    
    'Note exactly the same select statement as would be used in a relational database

    Ado_data.Open "select name, salary from employee.csv e, salary.csv s " & _
    "where e.id=s.id", adOpenStatic, adLockOptimistic

    If Ado_data.RecordCount < 1 Then
        MsgBox "No data found"
        Exit Sub
    End If

    'Go to the first record in the set and loop around till no more available

    Ado_data.MoveFirst
    For i = 0 To Ado_data.RecordCount - 1
        MsgBox (Ado_data.Fields.Item(0).Value & " " & Ado_data.Fields.Item(1).Value)
        Ado_data.MoveNext
    Next

    Ado_data.Close
    db_Data.Close

End Sub

Thanks again for your help in trying to understand this,

darkhat
 
Upvote 0 Downvote
Set db_Data = New ADODB.Connection

You must qualify all your references properly

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote

Code: 
Dim intID As Integer 'ID from manager record
Dim dbFile As String 'Location of our Access database file
Dim db_Data As ADODB.Connection
Dim Ado_data As ADODB.Recordset
Dim sSQL As String 'Our SQL query

dbFile = Me!txtFileLocation
intID = Me!ID

MsgBox "The ID being used is: " & intID 'Error Check
MsgBox "Using dbFile: " & dbFile 'Error Check

'Connection    
Set db_Data = New ADODB.Connection
db_Data.CursorLocation = adUseClient
db_Data.Open "PROVIDER=MSDASQL;dsn=C:\Documents and Settings\treptor\Desktop\RaysDB;uid=;pwd=;database=;"

'Recordset
sSQL = "SELECT name, salary " & _
       "FROM employee.csv As e, salary.csv As S " & _
       "WHERE e.id=s.id;"

Set Ado_data = New ADODB.Recordset
With Ado_data
   .CursorLocation = adUseClient
   .CursorType = adOpenStatic
   .LockType = adLockReadOnly 
   .Source = sSQL 
   .Open
   If Not (.BOF And .EOF) Then
      MsgBox "No data found"
   Else
      Do While Not .EOF
         MsgBox .Fields(0) & " " & Fields(1)
         .MoveNext
      Loop
   End If
   .Close
End With
Set Ado_data = Nothing
db_Data.Close
Set db_Data = Nothing

End Sub

A very nice recent thread222-1363106 to study, btw on cursors, locks etc.
Example is (very) analytical for demostration reasons ...
 
Upvote 0 Downvote
Geoff,

Can you explain to me how the ADODB.Connection works. I really don't understand it.

What is the difference between: New ADODB.Connection
and CreatObject("ADODB.Connection") don't they both create new objects?

thanks,

Darkhat
 
Upvote 0 Downvote
That is not your issue - your issue is that you didn't qualify the "Connection" as an ADODB.Connection. You can have lots of different types of connections, therefore you must explicitly tell VBA what kind of connection it is.

How to explain what an ADODB connection is....well....the clue is in the name - it is an object that sets a reference to a connection to a database

To get data from a database, you generally need 4 things

1: The database object
2: The connection object (this should include any permissions)
3: A valid SQL string
4: a recordset object to hold the results of the SQL execution

All the objects need to be explicitly defined

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
One more question, say I did a
Code: 
Set objConnection = CreatObject("ADODB.Connection")

If I wanted to open a database in an other location to store it in objConnection I would do a:

Code: 
objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = c:\test.mdb"

But how would I open a table in the current database that I am in? Say I have a Table called Employees????

thanks,

darkhat
 
Upvote 0 Downvote
If it is in the current database, you do not need a connection but how you reference the table will depend on what you are trying to do with it...

If you want to query a table in your current database, I think you can just use the database object to run the SQL.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
darkhat01


If you still want an ADODB.Connection for the currently open db then

Dim Cnn As ADODB.Connection
Set Cnn = CurrentProject.Connection

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
463
remeng
remeng
Andrzejek
  • Locked
  • Question
Data Type in ADODB.Recordset
Replies
6
Views
281
strongm
strongm
morechocolate
  • Question
run-tune error 3704 operation is not allowed when the object is codes: I don't think so.
Replies
7
Views
483
morechocolate
morechocolate
feipezi
  • Locked
  • Question
Hoe to fix run time error 2147217900 (80040e14) syntax error in Order By clause 1
Replies
6
Views
829
Andrzejek
Andrzejek
EliseFreedman
  • Locked
  • Question
booking tool - all day time slots
Replies
3
Views
206
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top