Fra lokal database til OBDC

what doesnt work?

Alt dette er lavet i en test database, men jeg skal have flyttet koden over i en OBDC sammenkædet database. Har fået ændret koden til de forskellige tabels og forspørgsler og rettet forespørglerne så det virker, men tror at fejlen ligger i koden ovenfor, pga tror ikke den nu kan referere til de read only tabeller jeg har sammenkædet.

Men har ikke så meget forstand på det.

gik bare ud fra at Dim db as DAO.Database referer til en lokal database, og så ikke kna håntere sammenkædet tabeller?

Its a while since I made Access dB's with linked ODBC tables, but I dont think there is much difference.

it actually says


Fault:

compile error
User-defined type not defined.

then it highlights this line

Public Function Sendsms()

but I dont think that has anything to do with ODBC!

Do you have an End Function?

strange, k, then I have to look into it. but will return, its very helpful inhere.

will respond when I have checked it. will be en a couple of hours I guess.

isnt the code you gave above only a part of your code? The function needs a


Public Function Sendsms()

'Code goes here

End Function

its the same code from before but just changed to fit to other tables and queries.

But now its with ODBC chained tables.

gotta go for about an hour, so wont reply, meanwhile

Omformulering af spørgsmål

Denne kode har virket på en standard database, men nu har jeg lagt den sammen med tabeller der er sammenkædet fra en ODBC database, og opdateret de forskellige queries og moduler.

Nu kommer denne fejl:
compile error
User-defined type not defined.

Og highlighter denne linje

Public Function SendEmail()


Jeg har en macro til at aktivere modulet som afspiller kode =SendEmail()


Her er den komplete kode.

for query:

SELECT dbo_VEHICLE.BASIS_NUMBER, dbo_VEHICLE.REGISTER_NUMBER, dbo_VEHICLE.CHASSIS_NUMBER, dbo_VEHICLE.MODEL_TEXT, dbo_CUSTOMER.STREET_ADDR, dbo_CUSTOMER.NAME, dbo_CUSTOMER.ADDR_2, dbo_ZIP_ADDR_NEW.TOWN, dbo_CUSTOMER.ZIPCODE, dbo_VEHICLE.MISC_6_CF, dbo_VEHICLE.MISC_7_CF, dbo_VEHICLE.NEXT_SERVICE_TXT, dbo_VEHICLE.LAST_SERVICE_TXT
FROM (dbo_VEHICLE INNER JOIN dbo_CUSTOMER ON dbo_VEHICLE.USER_CODE = dbo_CUSTOMER.CUSTOMER_NUMBER) INNER JOIN dbo_ZIP_ADDR_NEW ON dbo_CUSTOMER.ZIPCODE = dbo_ZIP_ADDR_NEW.ZIPCODE;








for Modul:

Option Compare Database
Option Explicit

Public Function SendEMail()

Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String

Set fso = New FileSystemObject

 

  Set MyOutlook = New Outlook.Application


    Set db = CurrentDb()
    Set MailList = db.OpenRecordset("SELECT * FROM MyEmailAddresses WHERE dbo_VEHICLE.REGISTER_NUMBER Like '*" & InputBox("indtast") & "*'")

    Do Until MailList.EOF

       
       
        Set MyMail = MyOutlook.CreateItem(olMailItem)
           
            MyMail.To = MailList("LAST_SERVICE_TXT")
                       
            MyMail.Subject = Subjectline$
           
            MyMail.Body = MyBodyText

           
            MyMail.Display


       
    MailList.MoveNext

Loop


Set MyMail = Nothing



Set MyOutlook = Nothing

MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing

End Function

Try the following

1: Does the query work alone?
2: Do you have a reference to DAO and Outlook? (tools + references)
3: If you have these reference then comment out ALL lines of code.
4: Start from the first line and uncomment on line at a time.

To see if you can find which line is giving the error. I am sure it is NOT the function SendEMail

I dont think you are are using fso so you can also remove it.

Dim fso As FileSystemObject

Set fso = New FileSystemObject

1: Yes
2: reference?

How should I reference them?

isnt the reference in the first part of the kode?

I guess it's those references because when I comment everything out exept these lines below. It still makes the same fault.

so how does the reference work?


Public Function SendEMail()

Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String

end function

Im a fool, forgot the reference, fixed now...... but it still doesnt wanna work

what if you comment ALL lines including your variables?

Public Function SendEMail()

end function

to sum up.

1: Yes the query works alone.
2: references are correct (now).
3: Tried to comment everything out, then it could run.
4: If I uncomment the first line or every line of the "Dim...." then it makes a fault

If I comment those to lines and leave the rest dim lines uncommented then i runs.
like this


Public Function SendEMail()

'Dim db As DAO.Database
'Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String

end function

are you 100& sure you have a reference to DAO ?

Damn I'm a fool.... Ofcource that one should be added too as a reference.

Now it all works.....

I could kiss you ;-)

But I wont instead you have earned those points. so plz make formal answer...

:o)

But I find it strange that the error isnt on the line
Dim db As DAO.Database

none the less, it working and thats whats important.

Could you help me about a small problem, dont know if its eaey to do, but when i search, I have to you capital letters, because its stored in the tables like that. Is there a way so its not nessesary to use capital letters when searching.

You should be able tous ethe Format function to convert to uppercase. I'm off out now, will take a look later.


User-Defined String Formats (Format Function)
     

You can use any of the following characters to create a format expression for strings:

Character Description
@ Character placeholder. Display a character or a space. If the string has a character in the position where the at symbol (@) appears in the format string, display it; otherwise, display a space in that position. Placeholders are filled from right to left unless there is an exclamation point character (!) in the format string.
& Character placeholder. Display a character or nothing. If the string has a character in the position where the ampersand (&) appears, display it; otherwise, display nothing. Placeholders are filled from right to left unless there is an exclamation point character (!) in the format string.
< Force lowercase. Display all characters in lowercase format.
> Force uppercase. Display all characters in uppercase format.
! Force left to right fill of placeholders. The default is to fill placeholders from right to left.