Insert into access database sql query

[sebastienkanj]

Hello! I need some help concerning the sql query to add a value of a textbox into the "hyperlink" field in my access database.

That's how it has to work: I have a userform with a listbox I choose a device I have the ID of this device in a textbox , I want to add the texbox value in a column of the row that contain the device with the right ID!

I'm trying this:

Please Login or Register  to view this content.

So: device is the name of my table
pathbgva3 the name of the textbox
ID is the name of the field in access database
Hyperlink is the field in access database where i want to add the textbox value


Can someone help me with this?

[Kyle123]

Is this in access or excel?

[sebastienkanj]

The code is in excel button that has to insert the value of the textbox into a specific row in access in a specific field

[Kyle123]

Ok, can you post a bit more of your code, that looks like access code rather than Excel. Are you using DAO?

Wouldn't:

Please Login or Register  to view this content.

work?

[sebastienkanj]

Please Login or Register  to view this content.

This is the code of the button that should insert the hyperlink showed in the textbox (pathbgva3) into the field Hyperlink in access in the specific row where ID field (in access) is equal to the value of the texbox(IDbgva3)

[Kyle123]

I think you'd be better off using ADO or DAO, they would be much more efficient than using an Access instance and wouldn't require you to have access installed on the client machines.

Something like:

Please Login or Register  to view this content.

you'll need to add a reference to Microsoft Active Data Objects for the above

[sebastienkanj]

I have Microsoft Active X Data Objects!

Is it the same?

[Kyle123]

yup, that's the one

[sebastienkanj]

I have an error message:

"synthax error (missing operator) in the in the expression..." and the expression is the path to the file that I want to add in the database (I want to put that hyperlink in the field in access)

[Kyle123]

So you've getting an error on the execute line?

can you post the value of the path?

[sebastienkanj]

Yes exact on the execute line!

Here is the value of the path:

C:\Users\seb\Desktop\HANDKE Medizintechnik\Projets\GMAO avec access\Projet VBA M1\Interface2\Hyperlink files\Hyperlink test file.xlsx

[sebastienkanj]

I tried this but it's not working...

Please Login or Register  to view this content.

I have the same error message for the file path!!

[Kyle123]

and the ID is a numeric field?

If so copy and paste the sql you create in Excel and copy and paste it into access, often the error messages are more useful - you only have one set of quotation marks right?

[sebastienkanj]

Yes the ID is an autogenerated number (unique number) in access everytime I add a device to the database!!

Where do I have to paste the code in access?

[Kyle123]

Create a new query and go to sql view.

To get the sql, put

Please Login or Register  to view this content.

in your code, the sql string will then appear in the debug window

[sebastienkanj]

I've tried to paste the code you gave me and execute they said it was an invalid code

[Kyle123]

Who said it was? eh?

Please Login or Register  to view this content.

[sebastienkanj]

I've try to create a query from access option and the said to me that!

[Kyle123]

I think we're misunderstanding each other.

When you create a query, go into sql view. You can then insert the sql, so it will look something like:

INSERT INTO Devices (Hyperlink) VALUES ('C:\Users\seb\Desktop\HANDKE Medizintechnik\Projets\GMAO avec access\Projet VBA M1\Interface2\Hyperlink files\Hyperlink test file.xlsx') 

But lets step back a minute here, what are you actually trying to do. INSERT queries do not have a WHERE clause, insert puts a new row in the table. If you are wanting to insert data into the hyperlink field of an existing row, you should be using UPDATE

[sebastienkanj]

yes exact I want to update an existing row ! That's why I used update first! Ok let's forget the insert into! and try update!

[sebastienkanj]

Actually it seems to wrok with this:

Please Login or Register  to view this content.

I tried it once it has added the path to the database in the right place!

[Kyle123]

Ok so how far does this get you?

Please Login or Register  to view this content.

[Kyle123]

your code is messed up, use either ADO or Access.Application, not both. I'd advise ADO as per my post above

[Norie]

Sebastien

Try this for the UPDATE query.

Please Login or Register  to view this content.

[sebastienkanj]

Ok so how far does this get you?

Please Login or Register  to view this content.

I have an error message with this code :

synthax error (missing operator) in expression 'ID='

[Kyle123]

Can you update the Debug.Print line to match the Execute line and paste the actual sql it generates in the immediate window here?

[sebastienkanj]

I 've corrested the line and it still give me the same error message! Is that what you asked me for?

I don't understand what do you mean by "paste the actual sql it generates in the immediate window "!

[sebastienkanj]

What about this code! It working with this code!

Please Login or Register  to view this content.

[Kyle123]

the the VBE, go to View - Immediate Window, if you don't already have it, it is a white box that you can type into.

when you run the code, it will put the sql into it. Copy that sql and paste it here

[sebastienkanj]

the the VBE, go to View - Immediate Window, if you don't already have it, it is a white box that you can type into.

when you run the code, it will put the sql into it. Copy that sql and paste it here

Please Login or Register  to view this content.

I thinnk the error is on the ID

[Kyle123]

That's an entirely different value for doneeID!?

that code is also slow, and requires everyone who uses your workbook to have access installed, if you're happy with that then go with yours.

Updated using your values:

Please Login or Register  to view this content.

[sebastienkanj]

I found the error it was on the "donneeID"

With this little change it works!

Please Login or Register  to view this content.

I have put

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

[Kyle123]

Great stuff glad you got it sorted. You might want to use CLng rather than CInt to prevent errors with you ever get a large amount of records in your table though

[sebastienkanj]

OK!
Thank you VERY MUCH for your help!!

I have a last wish to do but I don't know if I can write it here !

Now that I have the hyperlink, when I select the device with the hyperlink added in the database , I have this hyperlink in a textbox and I want to create a button when I press on it it opens the file!

I tried this :

Please Login or Register  to view this content.

[Norie]

Try this.

Please Login or Register  to view this content.

[sebastienkanj]

I have thi s message for this code : "Method or propriety not handled by object" (I'm translating the error message from french so maybe it's not the exact words)

[Norie]

Oops.

That's Access code.

For Excel replace Application with ThisWorkbook.

[sebastienkanj]

GREAT! Thank you ! It works!!
But when the file (it's an excel file) opens it doesn't appear because it's behind the userform how do you let the file be visible and the userform behind it so when I close the excel file I find back my userform?

[Norie]

If it's Excel files you are opening perhaps you should use Workbooks.Open rather then FollowHyperlink?

Please Login or Register  to view this content.

[sebastienkanj]

OK! I changed it! But I have put to hide the userform before openning the workbook and when I close the workbook I want to open back the userform !
Or just put the userform in background when workbook opens!

[Norie]

Give this a try.

Please Login or Register  to view this content.

I'm not sure how that will work with Excel files but for web pages it should open them in a new window.

[sebastienkanj]

OK! I changed it! But I have put to hide the userform before openning the workbook and when I close the workbook I want to open back the userform !
Or just put the userform in background when workbook opens!

Please Login or Register  to view this content.

doesn't work!

[Norie]

How do you show the form in the first place?

If it was with a button then you could just press the button again and the form should re-appear as it was.

[sebastienkanj]

I show the form with a button! But when I press the button to open my file it opens in the background unless I hide my form then the file will appear but when I wlose the file the userform is closed!

[Norie]

What happens if you press the button that shows the form after you've closed the file you've opened?

[sebastienkanj]

I Don't have the userform if the file is openned!
For example if I want to see the file I have to hide the userform otherwise if I keep the userform showed I can't see the file!!

[sebastienkanj]

Actually I have a weir problem! the copy fonction that insert the hyperlink to access database is no longer working! Yesterday when I tired it it was working!
Do anyone know why?
(I can send the files by mail if needed)

[sebastienkanj]

How can I make my userform set to the background and the excel workbook that im oppening in the foreground?

[Norie]

Sebastien

Which question do you want addressed first?

The problem with the database or the one with the userform?

By the way, why are you opening other workbooks?

Are you doing something with them?

[sebastienkanj]

In fact the userform shows me in listbox the devices that have to be checked so i choose one select it and if there is already an excel file linked to the device (its a test report on an excel file) i open it and edit it then save it close it and i want to get back to the maon menu in the userform called interface2!