1

Hi everyone

I’m writing a tiny database that will be shared across 20 people, I’ve got the back end in MS SQL and now want to write the front end in Access (then roll out via a run time).

I think I’m being stupid here, I’ve written all my SQL statements ready to paste in to buttons and so on. Am I right in thinking I should be pasting my SQL code within an Access query, and then assigning that to a button? As when I’ve run it, it errors. For example a bit of code to update a drop down list:

USE Database1;
delete from [table1].[dbo].counsel;
insert into [database1].[dbo].counsel (counsel) select name FROM [database2].[dbo].[centralcontact] where used=‘1’

I only have a single ODBC connection to Database1 (Didn’t know if I needed another connection to database2)

4 Spice ups
2

I don’t think you need the “USE” statement in Access.

2 Spice ups
3

I know a bit of sql, and I know a bit of access, but, I’ve never connected them both before

1 Spice up
4

Also, I don’t think you can have multiple queries run like that. Save them as separate queries, and call them in either VBA or a macro.

2 Spice ups
5

Blurghhh, I’m wondering if it’s quicker to write it all in access and share the data file out

1 Spice up
6

Never worked with SQL before, but won’t linking the tables through get external data do the trick?

2 Spice ups
7

Hi guys

Found what to do, I’ve come up against a limitation of access only being able to run a single sql statement at a time within the query, crazy!

1 Spice up
8

That is why I told you to save them separately and run both of them in a macro or vba.

2 Spice ups
9

OK, well, I’m learning

I’ve written this and trying to understand how to add an odbc string, so I don’t need to faff with odbc on every machine

Private Sub Command0_Click()
DoCmd.RunSQL “delete * from [sql-casecodes]”

DoCmd.RunSQL “insert into [sql-casecodes] (CaseCode,CaseName) values select casecode, name FROM [ODBC;Driver={SQL Server};Server=sql1;Database=cms;Trusted_Connection=Yes];.cms_case”

End Sub

I’ve seen a few ways of writing it and tried them all, but think I’m writing it down wrong. When run, I get a run-time error of 3134 / syntax error in INSERT INTO statement

I’m sure it’s something obvious but I can’t see it

1 Spice up
10

You really shouldn’t have to jump through this many hoops. Once you connect to the database just link the tables and treat them like any other access table.

1 Spice up
11

I have a knack of making things difficult for myself… :\

At the moment I have two databases on a single sql, but with the odbc I’d have to create two links on each machine I want to push this application out to (about 30) and thought if I coded it internally, it’s one less thing to mess around with on the machines?

1 Spice up
12

Are your users on your domain? If so, you can push out the DSNs via Group Policy.

1 Spice up
13

Hi Robert

I could do, I just wanted it all self contained

1 Spice up
14

Understood. Try removing the semi-colon from this piece of the code: Yes];.cms_case

Just a guess that it doesn’t like that.

1 Spice up
15

Doesn’t like it :frowning:

I’ve looked at the code for so long it’s all starting to look the same

1 Spice up
16

What version of SQL Server are you using? Perhaps you need to use the SQL Native Client:

Driver={SQL Server Native Client 11.0};Server=myServerAddress; Database=myDataBase;Trusted_Connection=yes;

1 Spice up
17

It’s on a SQL 2005 (not express)

Didn’t like that code either lol

1 Spice up
18

You may need to do the whole create connection, open it, execute query, etc route then. I’ve never tried to use the connection string inline like that and I’m not sure you can or what the right syntax is if you can.

There is an example you can base it off of here (it’s VBScript, not VBA, but close enough):

1 Spice up