Access

jeanniejones · 2017-11-15T16:52:52.000Z

The IT dept had to change from SQL 2005 to SQL 2010 i have a database that was built by a previous Database Developer no longer here. When I run The Database the auto-exec Macro i get an error This Expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements.

How do I fix this error so the Department users can use the Database?

ryanrobinson4 · 2017-11-15T17:16:20.000Z

Are you using a ODBC? If so, check to see if you have the correct drivers installed for the new SQL server version on your computer. If this doesn’t help, can you give us more information about the environment?

michaelmcewen · 2017-11-15T17:22:38.000Z

I’m figuring that it’s using ODBC drivers, and the tables need to be relinked. Depending on which version of Access you’re using, you’re going to need to find the Linked Table Manager. In 2016, it’s under the tab External Data. In earlier version, it’s either under Database Tools or more often as an Add-In.

You’ll need to have already created a data source that references the new SQL Server (using it’s drivers) that you’re going to link to.

If the database opens to a menu and you don’t have access to the normal Access menus then try opening it while pressing the Shift Key.

If you are still having troubles, then let us know the version of Access and any info about the database file (like the extension) that you might have.

browncoatsgt · 2017-11-15T17:22:58.000Z

Also,

Please show us the code in the autoexec bat.

jeanniejones · 2017-11-15T18:18:18.000Z

There no code just macro i notice the person who developed it didnt use VBA

jeanniejones · 2017-11-15T18:19:46.000Z

How do I do created a data source that references the new SQL Server (using it’s drivers) that you’re going to link to.

browncoatsgt · 2017-11-15T20:17:10.000Z

jeanniejones:

There no code just macro i notice the person who developed it didnt use VBA

macros are code

what does the macro do? what steps are in it?

michaelmcewen · 2017-11-15T21:20:43.000Z

jeanniejones:

How do I do created a data source that references the new SQL Server (using it’s drivers) that you’re going to link to.

It depends on versions of everything. What’s your OS and what version of Access…also if 32 bit or 64 bit.

Usually the data source is under Administrative Tools and is called Data Sources. If your Access is 32 bit but you have it on a 64 bit Windows 7/10 setup, then you need to create a separate link for the 32 bit Data Source tool which would be at : C:\Windows\SysWOW64\odbcad32.exe.

You’ll want to create a System DSN so select the System DSN tab.