insert data into a sql server table from an access form where tables are not linked using vba dao

[hansdbase]

I do not want to link any tables from access to sql server. I simply want to use an access form to enter data that is then inserted into a table in a sql server database. I have been connecting with an ODBC driver so I can retrieve data from the SQL Server table from Access, but I can't seem to be able to insert data entered from the access form back to the sql server table. Any help on how I can do this? Thanks.

[Gicu]

There are a few methods such as writing a pass-through query, binding the form to an ADO recordset (https://learn.microsoft.com/en-us/of...-ado-recordset) or probably the easiest would be to temporarily link the SQL table (in VBA) run your Insert SQL statement then delete the link.

Cheers,

[ranman256]

Why are you making it difficult?
link the table,add your data.

[hansdbase]

Thank you. That really helped. I ran an insert and then immediately deleted it.

[hansdbase]

Fair question. Because I don't want the users to see or have access to the table. Does that seem reasonable? Or is there a way to have the tables persistently linked without the users being able to access the tables?

[hansdbase]

Fair question. Because I don't want the users to see or have access to the table. Does that seem reasonable? Or is there a way to have the tables persistently linked without the users being able to access the tables?

[Micron]

research using a term similar to "how to secure ms access database"
Basic users should never be able to get at the tables - at least not with any permissions that allow edits/deletions.
You don't necessarily have to implement every idea you find.

Here's one
https://www.techrepublic.com/article...cess-database/

[Gicu]

@hansdbase: I used the pass-through and link\delete approach for a similar reason - managing login\user info tables and not wanted them to be available in the front-end navigation bar.

Cheers,